Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / March 2006

Tip: Looking for answers? Try searching our database.

Date query criteria

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Andy Bailey - 30 Mar 2006 14:26 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.