Between Dates or All Dates if No Date Entered

[PatrickA]

All,

I can't find info on how to do this in Access Help, and am hoping it
can be done...

I have a query with the following in the Criteria field for my Export
Date:

=#5/1/2009# And Between [Enter a Starting Export Date] And [Enter an Ending Export Date]

How do I enhance this Criteria so that
If the user does not enter a Starting Export Date, the query retrieves
all records back to 5/1/2009.

If the user does not enter an Ending Export Date, the query retrieves
all records forward to the latest record after 5/1/2009.

If the user enters neither a Starting Export Date or an Ending Export
Date, the query retrieves all records from 5/1/2009 to the latest
record.

I've tried using "*", but did find info that that will not work with
Between.

Thanks!

Patrick

 

[John Spencer MVP]

IF the field always has a value - no nulls or if you don't care about null
values then you can use something like the following.

BETWEEN NZ([Enter a Starting Export Date],#1/1/1900#) A
nd NZ([Enter an Ending Export Date],#12/31/3000#)

If no start date is entered then all records dated after Jan 1, 1900 to the
End date will be returned.

If no end date is entered then all records between the start date and Dec 31,
3000 will be returned

If both are left blank then all records between Jan 1, 1900 and Dec 31, 3000
will be returned.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

All,

I can't find info on how to do this in Access Help, and am hoping it
can be done...

I have a query with the following in the Criteria field for my Export
Date:

=#5/1/2009# And Between [Enter a Starting Export Date] And [Enter an Ending Export Date]

How do I enhance this Criteria so that
If the user does not enter a Starting Export Date, the query retrieves
all records back to 5/1/2009.

If the user does not enter an Ending Export Date, the query retrieves
all records forward to the latest record after 5/1/2009.

If the user enters neither a Starting Export Date or an Ending Export
Date, the query retrieves all records from 5/1/2009 to the latest
record.

I've tried using "*", but did find info that that will not work with
Between.

Thanks!

Patrick

 

[ken]

Alternatively:

PARAMETERS
[Enter a Starting Export Date] DATETIME,
[Enter an Ending Export Date] DATETIME;
SELECT *
FROM YourTable
WHERE ([Export Date] >= [Enter a Starting Export Date]
OR [Enter a Starting Export Date] IS NULL)
AND ([Export Date] < DATEADD("d",1,[Enter an Ending Export Date])
OR [Enter an Ending Export Date] IS NULL);

Two points about this:

1. Its best to always declare date/time parameters as they might
otherwise be misinterpreted as arithmetic operations and give the
wrong results. You can do this in SQL view as above or in query
design view.

2. Defining the date range in the above way is more bullet-proof than
a BETWEEN….AND operation as it caters for any date/time values in the
column which might inadvertently have a non-zero time of day element.
Unless you have taken specific steps in the table definition to
disallow such values you cannot guarantee that none are present in the
column. Any such values on the final day of the range would not be
returned by a BETWEEN….AND operation, whereas defining the range by
dates on or later than the start date and before the day following the
end date is guaranteed to pick them up.

Ken Sheridan
Stafford, England

All,

I can't find info on how to do this in Access Help, and am hoping it
can be done...

I have a query with the following in the Criteria field for my Export
Date:

=#5/1/2009# And Between [Enter a Starting Export Date] And [Enter an Ending Export Date]

How do I enhance this Criteria so that
If the user does not enter a Starting Export Date, the query retrieves
all records back to 5/1/2009.

If the user does not enter an Ending Export Date, the query retrieves
all records forward to the latest record after 5/1/2009.

If the user enters neither a Starting Export Date or an Ending Export
Date, the query retrieves all records from 5/1/2009 to the latest
record.

I've tried using "*", but did find info that that will not work with
Between.

Thanks!

Patrick

 

[PatrickA]

Thanks Ken!

Thanks John!