Returning records if Query Criteria is left blank

[Antonio]

I have a query that prompts the user for criteria before
returning any data. I found out that by placing the
copying the criteria into the "Or" cell below
the "Criteria" cell in the query design grid, and then
typing "Is Null" after the [criteria], all records will
be returned when the user does not type anything into the
criteria prompt. Is it possible to set this up so that it
works with a query that has more than one criteria
prompt?

Example:
query contains the fields "Date", "UserID".
Criteria is Between [Enter Start Date]And [Enter End
Date] for the "Date" field.
Criteria is [Enter User ID] for "UserID" field.

Is it possible to set it up so that when if the user
types in the Start Date and End Date, but leaves the
Enter User Id prompt blank, only those records specified
by the start and end date display, but all users are
displayed? and vice versa?
Ive tried to place the []Is Null on under both criteria
cells but it seems that when you place a []Is Null in any
of the "Or" cells, any other criteria parameters you may
have set are cancelled out.

TIA

 

[Ken Snell]

Use this criteria for the User ID field:

[Enter User ID] Or [Enter User ID] Is Null

 

[Antonio]

Thank you so very much Ken! You've been a great help.

-----Original Message-----
Use this criteria for the User ID field:

[Enter User ID] Or [Enter User ID] Is Null

--
Ken Snell
<MS ACCESS MVP>

I have a query that prompts the user for criteria before
returning any data. I found out that by placing the
copying the criteria into the "Or" cell below
the "Criteria" cell in the query design grid, and then
typing "Is Null" after the [criteria], all records will
be returned when the user does not type anything into the
criteria prompt. Is it possible to set this up so that it
works with a query that has more than one criteria
prompt?

Example:
query contains the fields "Date", "UserID".
Criteria is Between [Enter Start Date]And [Enter End
Date] for the "Date" field.
Criteria is [Enter User ID] for "UserID" field.

Is it possible to set it up so that when if the user
types in the Start Date and End Date, but leaves the
Enter User Id prompt blank, only those records specified
by the start and end date display, but all users are
displayed? and vice versa?
Ive tried to place the []Is Null on under both criteria
cells but it seems that when you place a []Is Null in any
of the "Or" cells, any other criteria parameters you may
have set are cancelled out.

TIA

.

 

[Ken Snell]

You're welcome.

Thank you so very much Ken! You've been a great help.

-----Original Message-----
Use this criteria for the User ID field:

[Enter User ID] Or [Enter User ID] Is Null

--
Ken Snell
<MS ACCESS MVP>

I have a query that prompts the user for criteria before
returning any data. I found out that by placing the
copying the criteria into the "Or" cell below
the "Criteria" cell in the query design grid, and then
typing "Is Null" after the [criteria], all records will
be returned when the user does not type anything into the
criteria prompt. Is it possible to set this up so that it
works with a query that has more than one criteria
prompt?

Example:
query contains the fields "Date", "UserID".
Criteria is Between [Enter Start Date]And [Enter End
Date] for the "Date" field.
Criteria is [Enter User ID] for "UserID" field.

Is it possible to set it up so that when if the user
types in the Start Date and End Date, but leaves the
Enter User Id prompt blank, only those records specified
by the start and end date display, but all users are
displayed? and vice versa?
Ive tried to place the []Is Null on under both criteria
cells but it seems that when you place a []Is Null in any
of the "Or" cells, any other criteria parameters you may
have set are cancelled out.

TIA

.