Date query criteria

[Guest]

I'm trying to put together a query that will find records between two dates.
The two dates are entered via a form and, if cleared, should return all
records.

So far I have (using previous posts as I'm a total novice) got:-
Is Null Or Between [Forms].[GetDiags].[FromDate] And
[Forms].[GetDiags].[ToDate]

The problem with this is that when the date entries on the form are cleared
and I run the query I only get records that don't have a date entry whereas I
would want all records to be returned.

Could anyone be kind enough to point me in the right direction? Thanks in
advance.

 

[Allen Browne]

Switch the query to SQL View (View menu, when in query design.)

In the WHERE clause you will see something like this:
(Table1.Date1 Is Null) OR
(Table1.Date1 Between [Forms].[GetDiags].[FromDate]
And [Forms].[GetDiags].[ToDate])

Change it to:
(([Forms].[GetDiags].[FromDate] Is Null) OR
([Forms].[GetDiags].[ToDate] Is Null) OR
(Table1.Date1 Between [Forms].[GetDiags].[FromDate]
And [Forms].[GetDiags].[ToDate]))

If either text box is null, the query returns all records. If both boxes
have a value, the query returns only dates between the values.

A more flexible solution might be to build the filter string dynamically
from just the boxes that have a value. Example in Method 2
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

For a more comprehensive example, showing how to filter a form based on any
combination of text boxes, download this example (for Access 2000 and
later):
http://allenbrowne.com/unlinked/Search2000.zip

 

[Wayne Morgan]

One more question, what do you want to do if only one date is entered?

Assuming if one date is cleared, they will both be cleared, try:

(Between [Forms].[GetDiags].[FromDate] And [Forms].[GetDiags].[ToDate]) Or
([Forms].[GetDiags].[FromDate] Is Null And [Forms].[GetDiags].[ToDate] Is
Null)

Also, you'll need to define the parameters as Date/Time data type. To do
this, open the query in design view and go to Query|Parameters... on the
menu bar. In the dialog, enter the parameters and set the data types as
follows:

[Forms].[GetDiags].[FromDate] Date/Time
[Forms].[GetDiags].[ToDate] Date/Time

 

[Guest]

Wayne

Many thanks - worked a treat. Appreciate your time.

Andy

 

[Guest]

Allen

Thanks for the post - I will look up the examples as you kindly suggest.

I've had a few occasions to ask questions in this group and I'm always
amazed at the speed and helpfulness of you all.

Andy