Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Developer Level 3   dismiss
 
 

< Previous: Access Beginner 4

Next: Access Beginner 6 >

Access Beginner Level 5
Beginner Microsoft Access Tutorial - 1 Hour, 14 Minutes

 
 
This Microsoft Access video tutorial picks up where Level 4 left off. This class covers query criteria in a lot more depth, parameter queries, wildcards, and lots more. Topics include:
 
  - Query Criteria
  - Multiple AND, OR Conditions
  - Inequalities
  - Records Between Two Dates
  - Dealing with Null Values
  - Wildcards and LIKE

  - Parameter Queries
  - Top X Values
  - Formatting Query Field Output

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first lesson of this course (free of charge), or scroll down for more information.




 

Access 2010 Beginner Level 5
Description: Access 2010 Beginner Level 5
Versions: Recorded with Microsoft Access 2010
Works fine with Access 2013 and 2007
Pre-Requisites: Access 2010 Beginner Level 4
Running Time: 1 Hour, 14 Minutes
Cost: $12.99


This class picks up where Level 4 left off. We will begin by learning a lot more about query criteria. We will learn how to use multiple AND / OR conditions in the same query. We will learn about the IN keyword to show records in a specified list of values. We'll learn about the BETWEEN keyword to view records between two values.

 

You will learn how to use the NOT keyword. We'll learn about the different kinds of inequalities that you can use in your query criteria. We'll learn about the Show Box which you can use to hide fields from the query output. This is handy if you need to hide sensitive data, like a credit limit, but still need to use that data for your criteria. You'll learn how to use date-based criteria with the Date() function to show, for example, all records in the past, or all records that are more than 30 days old.

 

We'll learn how to deal with empty/blank values with the Is Null and Is Not Null keywords. We'll cover wildcard characters and the LIKE keyword. This will allow you to find things like "all ZIP codes starting with 142," or "all customers with last names starting in S." We'll also see how to use wildcards to find records from a specific month.

 

Next we'll learn about Parameter Queries. These are extremely powerful. A parameter query allows the end-user to type in a value for a query criteria when the query runs. This way if you want to run reports for each state, for example, you don't have to make 50 different queries. The user can just specify "NY" or whatever state they want when the query runs.

 

Finally, in our Questions from Students lesson, we'll cover several different topics, like how to show the Top 10 Values (or Top X values, or Top X percent) in a query, what the drop-down box for the field name in a query is for, why there is a RUN button for queries (instead of just the datasheet view button), and how to format the ouput of query fields.

 

This is a great class for anyone who is wants to learn how to work with queries in Microsoft Access. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access 2010 Beginner Level 5

00. Introduction (6:49)

01. Query Criteria 1 (16:30)
Multiple OR Conditions
Delete a query
Rename a query
"NY" OR "PA"
"NY" OR "PA" OR "TX"
IN ("NY","PA","TX","CA")
Criteria OR Rows
From NY and are Active
AND condition across columns
AND across, OR down
(State=NY) OR (State=PA AND Active=TRUE)
Customers with Credit Limit over $1000
From NY with $1000 or anybody with $1500
AND OR inside of a single critera
BETWEEN keyword

02. Query Criteria 2 (15:40)
NOT Keyword
NOT "NY"
Inequalitites Review
NOT "NY" AND NOT "OH"
"NY" AND "PA"
AND limits query results
OR expands query results
Show box
Hide fields from query
Prevent odd names like "Field1"
Move a query column
Dates in pound symbols
<#1/1/2005#
Recent customers
BETWEEN two dates
d/m/y or m/d/y regional settings
Whether to include endpoints
BETWEEN #1/1/05# AND #12/31/05#
Dates with times can be tricky
>=#1/1/05# AND <#1/1/06#
<Date()
Customers before 30 days ago
<Date()-30
<Now()
Became customers yesterday
>=Date()-1 AND <Date()
03. Query Criteria 3 (8:47)
Is Null
Is Not Null
ZipCode LIKE "142*"
State LIKE "N*"
Email LIKE "*amicron*"
Date LIKE "*/2012"
Date LIKE "5/*/2012"

04. Parameter Queries (11:45)
Separate query for each state
[Enter the state]
Missing field names from tables
Parameters with other criteria
Multiple parameters
Between [Start Date] and [End Date]
Like [Company Name]
Teach your users about wildcards
XYZ*
Like "*" & [Company Name] & "*"
Will cover String Concatenation Later
Build report based on this query

05. Questions from Students (7:55)
Top X Values
Top X% Values
Bottom X Values
Drop-Down List in Query Columns
Change Field
Why the Run Button for Queries
Format Query Field Output
Property Sheet for Query Fields

06. Review (6:40)

 


 
Keywords: Query Criteria, Parameter Queries, Wildcards, LIKE, microsoft access tutorial, microsoft access 2010 tutorial, microsoft access 2010 training, AND, OR, BETWEEN, NOT, inequalities, inequality, show box, hide fields, move column, between dates, is null, is not null, like *, multiple parameters, top values, top x, format query field, property sheet
 
 

Student Interaction: Access 2010 Beginner 5

Richard on 7/23/2012:  This Microsoft Access video tutorial picks up where Level 4 left off. This class covers query criteria in a lot more depth, parameter queries, wildcards, and lots more. Topics include: - Query Criteria - Multiple AND, OR Conditions - Inequalities - Records Between Two Dates - Dealing with Null Values - Wildcards and LIKE - Parameter Queries - Top X Values - Formatting Query Field Output Click here for more information on Access 2010 Beginner Level 5, including a course outline, sample videos, and more.
Albert T on 7/26/2012: Richard I love this forum - small notes and remarks are very informative - like read some problems your students encounter and your solutions - keep them coming, Bert.

Reply from Richard Rost:

Thanks, Bert. I love the fact that we have a small community of users who help each other and I'm not the only one who posts all the answers. :)

Maurice C on 7/30/2012: Richard

Really enjoyed the Access 2010 Beginner 5 tutorial. With regards to parameter queries, would you be able to walk us through how to set up a form that works off a parameter query in a future lesson? For example, if some one wants to check their sales between 2 dates, a sales log query is created. Then feeding off that we create a form which has the fields for the start and end dates with a preview/print button so a report showing the sales between those dates can be generated.

Kind regards

Maurice

Reply from Richard Rost:

I cover this concept in Access 201 and 202, plus this Tip Video.

richard g on 9/7/2012: I have a customer table with 3 different phone number fields. I need to do a parameter query and enter a phone number once and have access search all 3 phone number fields for a match. Possible?

Reply from Richard Rost:

Very good question. Yes, it's possible, but it's a little beyond what I showed in today's class. You can either use a query-level parameter or an unbound form with a search field on it. I will cover both of these in the Expert classes, but this tutorial will show you how to set up the search form. You would just use the same text box as your criteria for each of the 3 different forms with an OR condition.

kalyan090 on 9/23/2012: Hi Richard
I have two columns of date fields(LeaveFrom and LeaveUpto) and I want a report based on date provided in query. Example: All leaves taken between i.e from 21 January 2012 upto 20 February 2012.



Reply from Richard Rost:

Your query criteria would be:

LeaveFrom: BETWEEN #1/21/12# AND #2/20/12#

OR (down a row)

LeaveUpTo: BETWEEN #1/21/12# AND #2/20/12#

This should catch anyone where their leave falls anywhere in that date range.


Connie on 9/28/2012: My question is about setting up the table. I have a large table to track capital work projects individually identified with a capital works number (in addition to the Access id). One column is for estimates and there are often estimates for multiple vendors. Should I have different fields, e.g. estimate 1, estimate 2, etc. or should I just make separate entries for each estimate. This would mean one capital work number might have 5 different access id numbers! Help!

Reply from Richard Rost:

Whenever you say that something can have multiple entries, you should be looking at another table. Even something like phone numbers. If you only want to stick to 1 or possibly 2 phone numbers, then multiple fields in your table are fine. However, if you want the ability to track 3, 4, 5 phone numbers (work, home, cell, fax, etc.) then you really should be looking at a SECOND table.

Now, I don't cover how to do this in my BEGINNER lessons. The first Expert lesson will talk about setting up relationships between multiple tables. If you want to learn it NOW, pick up a copy of Access 201 or my Relationship Seminar.

Connie on 9/29/2012: I will be setting up 6 tables. Each of these tables will have a common field (capital works number). Will this cause a problem when I want to generate reports pulling from several different tables?

Reply from Richard Rost:

I'd need to see your database (or at least your design schema) to tell you for sure... but generally it's OK to have the same number in several tables if it's a HUMAN-GENERATED number. For example, you might have a PART NUMBER that represents a specific product, and is assigned by the manufacturer, and you have it entered into several different tables (order, product, inventory, etc.)

Gary Wilson on 12/8/2012: Hello Richard.. question , I have this customer below in my table ,in my Query I have first name,last name,phone and state checked ,ran query about 10 times , saved and shut down access and I still can't get customer in my query run ...any suggestions ? I do get the other 3 NY customers just not this one.
Suzan Jones 123 Packing 101 Main St Buffalo NY suzan@amicron.com (562) 436-5146 25 10.00% 1/2/1998 1500 False

Reply from Richard Rost:

I really can't tell from just looking at this. Do you have any criteria in your query? Perhaps excluding NULL values? This data alone doesn't show me any reason why she wouldn't appear in the query results. If you want me to look at your database, feel free to submit it via the TechHelp page and I'll look at it when I get a chance.

Raul Pendas on 12/30/2012: I have a question whether you can use a query to find who has paid for an order and who has not paid.
I use the nil or not nil to get this?
Thank you for your attetion to this matter.
Raul

Reply from Richard Rost:

I would make an IsPaid field which is a Yes/No value. Then you can just make your query criteria for this field "Yes" or "No" depending on which you want to see.

Patrick Breuer on 1/23/2013: Richard,

when you run the query, there is always a default in the last record.
1.Is that default always there?
2.Is ithe defalut (say state) NY because that's the first state in the record?

Thanks,

Patrick

Reply from Richard Rost:

1. Yep. If you've set a default value in the table it will always show there.
2. No. That will ONLY show up there if you've set it as the DEFAULT VALUE property for the field in the table design. The value isn't ACTUALLY there until you start entering other data in the record.

Nick WILLIAMS on 1/29/2013: Richard, I have my own table that I am working on these days. In this 'people table,' I have 6 fields: ID number, status, prefix, first name, middle initial and last name. 11 records so far. I'd like to set up a query for null values.
I can see that 7 out of 11 records are missing the middle initial. I set up the query with the 6 fields, based on a table. All the Show boxes are marked. I run it and all 11 records appear. I save the query.
Then I type in "IsNull" for Criteria in the middle initial field (Design View). When I run the query again, no records appear. I expected 7 records to appear - those without a middle initial. What's wrong with this picture?

Alex Hedley on 1/31/2013: I think you're missing the space
IsNull => Is Null

IsNull([FIELDNAME]) is a function.

Jennifer Hull on 2/23/2013: query criteria lesson 3, 08.13 If you started you criteria with 5/*/2012, why does it come up with 7/21/2012. I thought the * only allowed any value in the 2nd area of the date, i.e. 17 and 21, and the other criteria should have been 5/ / 2012
Jennifer Hull on 2/23/2013: Thank you for making this so clear ( run vs view) !!!!
Jennifer Hull on 2/26/2013: Why do you call # "Pound" symbol. Is this common usage in the US? In SA and UK , pound is a crossed L, and # is Hash or used for number abreviation.

Reply from Richard Rost:

Yep. That's what we called it here in the US... pound sign, hash mark, etc. See this.

Jennifer on 2/27/2013: Thank you - explains why I was confused by Canadian friends! The ref article interesting. Am familiar with the sharp sign from music. In medicine # is also sometimes used as an abbreviation for a 'fracture'!!!!
Glynis Carey on 3/2/2013: Sorry, the time index for the video I was referring to is 14:04
Glynis Carey on 3/2/2013: Hi Richard,
When asking in query criteria for customers with a CreditLimit of >=1000 in State NY, OR (shown on separate row) >=1500 for all states, would NY data be duplicated if a NY customer has a 1500 credit limit?

Reply from Richard Rost:

The records will not be duplicated in your query results, no. You're just casting a bigger net, but you won't get the same fish twice.

Brian Merrick on 6/10/2013: When doing the query if you put the dollar sign and run the query will you get the error?

Reply from Richard Rost:

Where exactly do you mean? Please give me more details.

Brian Merrick on 6/10/2013: Can you set up a parameter query for a forum?

Reply from Richard Rost:

Do you mean "form?" You can create a parameter QUERY and then use that query as the record source for a FORM, yes. Then when you open the form, you'll be prompted for the parameter.

Ali Rewaished on 7/23/2013: Richard, I am using Access for longtime and i am comfortable with it. I v integrated Access with Sql Server for fast search and getting back the data.Do you have better better choice than this?

Reply from Richard Rost:

Nope. An Access front-end with an SQL Server back-end is about as good of a database solution as you can have.

Spencer Black on 8/1/2013: I like the reminder to remember the BETWEEN function. You are teaching me what I have learned (outside basic in Jr High 20 yrs ago) about computer programming.

It could be pointed out that the BETWEEN functions like [>=1500 and <=3000], not [>=1500 and <3000].

I love your lectures! Do you teach online university courses, or is this great and cheap tuition?

Access 2010 Beginner 5 Lesson 1 16:00/16:30

Reply from Richard Rost:

Thank you very much for your compliments.

Yes, you are correct that with NUMBER VALUES the statement:

BETWEEN 1500 AND 3000

is the same as

>=1500 AND <=3000

However, when you're dealing with Date/Time values you want to be careful, because:

BETWEEN #1/1/2000# AND #2/1/2000#

is the same as

>=#1/1/2000# AND <=#2/1/2000#

The problem is that #2/1/2000 5:00# is NOT included.

This becomes an issue when people use the =NOW() function to store values that are accurate to the SECOND in their date fields. Let's say you're working with invoices. You've got the default value of the DateTime field set to =NOW() because you might want to track your busy times of the day for taking orders (a reasonable request). Now when it comes time for your month end reporting, you specify a criteria of:

BETWEEN #1/1/2000# AND #1/31/2000#

Guess what happens... you now are missing all of the orders that occurred AFTER midnight on 1/31. And if you use:

BETWEEN #1/1/2000# AND #2/1/2000#

Now you might accidentally get any orders from 2/1 that came in at EXACTLY midnight (which could happen if a user types in a manual date of 2/1 and it defaults to midnight).

So... that's why I wanted to remind people that FOR PRACTICAL PURPOSES when dealing with DATES you want to say:

>=#1/1/2000# AND <#2/1/2000#

Thank you for bringing this up so I could clarify it. That's a VERY good point to bring up!

And, no, I've never taught at the university level. I attended college myself for a few years (University at Buffalo) back in the early 90s (yea, I'm old) but I was bored with the material they were teaching so I dropped out and started my own business.


John Borrelli. on 9/3/2013: you talked about running a report from a querry and touched on that relationship. I ran a querry that put the value in alphabetical order and it runs nice. I wanted to create that in a report but the report does not show in the same order?

Reply from Richard Rost:

A report will override any sorting you set up in a query. You need to set up a Sorting & Grouping Level in the report, which I cover in Access Expert 12.

Mohamed Zarzour on 10/2/2013: Richard,

Thank you sooo much. This is just very helpful

I have a project I am working on and I learnt a lot from your lessons. last week I did not know anything in access

Thanks again

Mohamed

Muhamad S on 12/8/2013: (My Id no 605706260)
1st Q. In Access 2003 Menu there is Open, Design,New
When we want to create a new form we click New , where we select Design View . Then in the Design View there are options to select either Table or Query . But I cant find these option in Access 2010 in the Field List. Pls advice me.

2nd Q. I am attached to a cardiac hospital and I have planned to do patient's database using access 2013 ,who are going for a bypass surgery ( that's why I enrol your course).
How to create a combo box for a query and the result displays in a Form .( instead of using Parameter query where need to key-in the search value)

Reply from Richard Rost:

How many of the lessons have you watched? Both of these questions are answered in the Beginner series.

Christine White on 12/16/2013: I would like to create a Part Number Search. Can you enter Several Parameter Queries in one Query?
Jenny Edwards on 1/5/2014: If you set up your access table by linking it to an excel spreadsheet, will there be any differences setting up queries and reports? Have to make changes to table in excel so not sure if it affects queries and report
Alice Morgan on 4/21/2014: Hi, Richard,

If I wanted to use a parameter query to isolate all records falling under that parameter, and that also match criteria in one of 2 other fields (an OR situation), how would I line that up?

Lisa P Christian on 5/2/2014: Hi Richard,
This is one of the most useful courses I've ever taken. Thanks sooooo much.
I'm not sure if I missed it but I've created a database based on the beginner course 1-5. I can't seem to find how to transfer it to another computer so my secretary can access it but not have access to my tables. Thanks.

Jack Glanzrock on 5/9/2014: Background
Currently I have a basic database with the following

Table 1 = "EmployeeT" with fields "Employee_ID", "First_Name", "Last_Name" etc.
Table 2 = "NewLabReportT" with fields "Report_ID", "Report_History", "Submitted_By", "Reviewed_By", "Date_Submitted", "Report_Title", "Abstract"

Form 1 = "MainMenuF" this is where I would like to have the search
Form 2 = "NewLabReportF" with fields "Report_ID", "Report_History", "Submitted_By", "Reviewed_By", "Date_Submitted", "Report_Title", "Abstract"
Form 3 = "EmployeeF" with fields "Employee_ID", "First_Name", "Last_Name" etc.

Problem:
I would like to have on the Main Menu a drop down list with all of the fields described in NewLaBReportF ("Report_ID", "Report_History", "Submitted_By", "Reviewed_By", "Date_Submitted", "Report_Title", "Abstract") and then allow the user to click a button to the side of it to alert a pop up.

In this pop up:

If they originally clicked "Report_Id" then have the message prompt then to "Enter a Report ID"
If they originally clicked "Submitted_By" then have the message prompt them to "Enter an employees Last Name"
etc...

Further More, after they enter the respective value in this box, have a FORM not a REPORT generate that lists all of the records that match the criteria. This way then can specifically click on each record individually and have it open directly up to it.

Mark Stute on 5/17/2014: I would like to have a parameter query where a date range can be specified by the user and a state specified. For example give me all the records between date one and date 2 AND where state is NY. Also, the user may wish to not enter a date 1 or date 2. How can I write a criteria in the date field to handle the situation where date 1 and date 2 are left blank? Thanks Richard.

Reply from Richard Rost:

Optional parameters get VERY tricky; so much so that I can't go into details here. It involves a whole bunch of IsNull functions and checking for empty strings. I cover techniques to deal with this in my Search Seminar.

Dawn on 5/21/2014: I have a table with transactions by date and account number. Can I create a query with multiple parameters? For example, I want to pull up a certain account number, and put in between start and end dates.

Reply from Richard Rost:

Yep. You can do this with a parameter query. Just put multiple parameters in the fields you want.

Erica Sena on 6/26/2014: Richard, Will Is Null look for "0 length" text strings?

Jeff S on 8/20/2014: Richard,
Love your videos and the "access" to your knowledge through this forum.
I have built a Parameter Query Form that I made a command button for on a "main menu" form like you created in your videos. I noticed one weird thing that happens that I spent 3+ hours trying to resolve on my own before giving up. The button works fine you get the prompt box for the value, but if you don't put something in and just hit "cancel" you get an error (#2001). How do I get it to just close the form if the user just hits cancel?

Thanks in advance for any guidance!!
Jeff

Reply from Richard Rost:

That is a little more complex. You can solve this problem with an IIF function (to check for a NULL result returned) and by using a FORM to get the parameter, like I show in this tip: Get a Value from an Open Form Field.

Joe Beniacar on 9/22/2014: Hi Richard.
In this section, we mention that to get all of the dates between two values, we could say >=[Start Date] and <[End Date], meaning ">=" is "after" and "<" is "before". But in the previous section, when we look at all people who worked here around 1 month, why do we say Date()-30, since ">" is after (and "<" is before), and we're looking for people who joined "after" 30 days ago (i.e. closer to now than 30 days ago)? Thank you for clarifying.
-Joe

Reply from Rick Rost:

Joe, since date values also include a TIME component, and we want to search between two dates, if I say

>=#1/1/2000# AND <#1/1/2001#

This will include all of the TIME values in 1/1/2000 but will NOT include, for example 1pm on 1/1/2001. You want to STOP at midnight on that date. This is why one value includes >= and the other is just <.

So, greater than 30 days ago would be

>Date()-30

This would not include exactly midnight on that date, however. I guess it all depends on whether or not you're dealing with TIME values, or just dates only.


Craig Brown on 10/13/2014: To Jennifer Hull:
The 7/21/2012 value is not a record yet. It is just the default value Rick has for the date function- (probably today's date used for the date he recorded the session).
Just like the NY being the default value for a state field.

Steve Reed on 12/22/2014: You say to right click on CustomersfromPA and then delete. When I right click, there is no delete option and the delete button on the keyboard doesn't work.?? simple question, but bugs me when I get stuck.

Reply from Alex Hedley:

Which version of Access are you using?
Is it a locked file i.e. accde
Can you add other objects to it like tables/forms?

Scott Shirley on 12/25/2014: When I open a form it asks for a parameter query based on a field that no longer exists. Other forms do not as for it. How can I get rid of it?

Reply from Alex Hedley:

Check what Query the Form is bound too and edit that, it'll include this old field.

claire brennan on 12/26/2014: Hi Richard
Would I be able to add a column to a query that adds x 12 weeks on to a date?
Thank you Clair

Reply from Alex Hedley:

Check out the Tip on Date Functions or look out for the next Expert class that will include lots more

marcia Nikolic on 3/25/2015: I set up a query and report based on today's date. When I run the query it doesn't add the records that have been put in the database since the query was designed. How can I get it to update?

Reply from Alex Hedley:

What is your Criteria?
Is it <= or BETWEEN?
You need to be careful with the bounds of your Date includes a Time.

simon b on 3/25/2015: If you are just using a Date you need to remember that for example 01/01/2015, as far as Access is concerned is the first of January 2015 at midnight, anything after midnight will not show up in the query, if you want to use a date as query criteria and you want data for that date to appear in the query output then you need to add one to the date (remember Access sees dates as numbers) or to get data for 01/01/2015 to show you would need to use 02/01/2015 (i am using UK date format) to get the data to include entries for 01/01/2015
Wayne Markel on 4/16/2015: Regarding parameter queries.

I want to have two fields in a query that require the user to enter a criteria.

My simplified table has two fields. PropertyID and TaxYear.

I want these results when the user fills out neither, either, or both input boxes.

This is my table:
PropertyID TaxYear AmountPaid
111 2012 20.12
111 2013 20.13
111 2014 20.14
222 2012 20.12
222 2013 20.13
222 2014 20.14

The are the results that I am looking for:
Parameters entered
PropertyID TaxYear

PropertyID TaxYear AmountPaid
111 2012 20.12
111 2013 20.13
111 2014 20.14
222 2012 20.12
222 2013 20.13
222 2014 20.14

PropertyID TaxYear
111 2012
Result
PropertyID TaxYear AmountPaid
111 2012 20.12
Result
PropertyID TaxYear
111
Result
PropertyID TaxYear AmountPaid
111 2012 20.12
111 2013 20.13
111 2014 20.14
Result
PropertyID TaxYear
2012
Result
PropertyID TaxYear AmountPaid
111 2012 20.12
222 2012 20.12

If this is beyond the scope of this lesson, please advise

Wayne Markel on 4/16/2015: Richard,

My apologies. Disregard previous question. I figured it out by concantenating "*" I am using a form to input criteria so it looks like this:

Like [Forms]![frmPaymentSearchByYear]![cboTaxYear] & "*"

Wayne

Jeanne Reichmann on 4/20/2015: Hi Richard. I have created a family database to "play" with. I would like to create a birthday query. I have everyone's birthday entered as mm/dd/yyyy. How do I separate them by month and day without having to make a separate field for the year?

Reply from Alex Hedley:

So you don't want to create a Field for Day, Month and Year using their respective functions?
Like this tip.

Ian Jemmett on 5/30/2015: Hi Rick. I found this (using Access 2013): If I use the '*' wildcard I don't need to put in the word 'Like'. Access does it for me when I hit 'Enter'.

Reply from Alex Hedley:

Thanks for the Tip Ian

Donald Pailen on 7/26/2015: I'd like to have automatic numbers show on a constantly growing membership roster that reflect the number of members at any given time. Obviously, the numbers in the auto ID column aren't appropriate for that. What's any easy way to do this?

Reply from Alex Hedley:

You could use this Row Number Tip.

Dennis Owens on 9/12/2015: I know I'm early in my lessons but I want to make sure what my goal is will be covered and I can use access to perform this project. I am the registration chair for an organzation that runs a convenntion once a year and I want access to maintain the data from the conventioneers from year to year. My hopes are to maintain and add memberships, to register them to this years convention by email or phone or some ID. Not necessary the auto ID and have that conventioneers name and info appear on screen and all I would do is add what they want to order and show that they are registered for this year. My convention is held in November and I would like to do this for my 2016 convention.

Reply from Alex Hedley:

The courses will teach you how to do this, you will learn techniques of how to build a Customer Service database, just take what you learn and apply it to your db needs.

Dennis O on 9/13/2015: Thank you, these lessons are great
Richard Lanoue on 1/11/2016: I have a two tables, a company table and a contact table. There is a foreignID in the company Table to connect it to each other. My problem is if I do a search using the SQL of this, and a company DOES NOT have a contact (the foreignID is null) it doesn't show up in the results. All the other records with a contact attached to it will but none of the Companies without a contact associated with it do... How can I make it so even the null value of a contact gets included with the not null ContactID?

Reply from Alex Hedley:

Check your Joins, if you double click on it there should be three options, choose show all records in Company, instead of the where it matches

Elizabeth R on 2/26/2016: I would like to have a parameter query around a Yes/No field. I used the criteria, Like "*" & [Enter True or False or Enter for All] & "*".
This returns all records if I hit return but no records when I enter True or False.. Thanks for any help. Elizabeth

Reply from Alex Hedley:

Which field are you performing the like against?

Elizabeth R on 3/7/2016: I have a field in my JobTrackingT called ActiveJob. It is a Boolean field. Thanks. Elizabeth

Reply from Alex Hedley:

Do you just want to return all yes or all no, if so don't make it a like, don't make it a parameter [] and use a Form with a check box and pass in the value from that form

Wim Keteleer on 4/18/2016: Is it correct that parameter queries do not allow logical expressions? In this example, "NY or TX" as [State:] will not work. Do I something wrong?

Reply from Alex Hedley:

You could add multiple [State] in subsequent rows of your Query so it pops up x times

Wim K on 4/20/2016: Correct, but not sufficient. The user should decide at runtime how many states she/he needs for his report.

Reply from Alex Hedley:

I'd create a form with checkboxes/multi select listbox for each state, allow the user to select which ones then want then pass those to the Query via an IN statement.

Timon on 5/8/2016: I don't get the property sheet for the field properties in the query to show. It only show's me the property sheet for the query itself.

(Yes I did right click on the field and then properties)

I use MS Access 2013

Reply from Alex Hedley:

Table Tools | Design | Property Sheet
Whilst the row/column is highlighted in design view

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP