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 - 30 Mar 2006 15:43 GMT
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

Signature
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
> I'm trying to put together a query that will find records between two
> dates.
[quoted text clipped - 13 lines]
> Could anyone be kind enough to point me in the right direction? Thanks in
> advance.
Andy Bailey - 30 Mar 2006 17:54 GMT
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
Wayne Morgan - 30 Mar 2006 15:45 GMT
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

Signature
Wayne Morgan
MS Access MVP
> I'm trying to put together a query that will find records between two
> dates.
[quoted text clipped - 13 lines]
> Could anyone be kind enough to point me in the right direction? Thanks in
> advance.
Andy Bailey - 30 Mar 2006 17:50 GMT
Wayne
Many thanks - worked a treat. Appreciate your time.
Andy