Date criteria on query if left blank (1 Viewer)

gojets1721

Registered User.
Local time
Today, 05:40
Joined
Jun 11, 2019
Messages
429
I have a date filtering criteria on a query where the user inputs the date period that they want to appear. Here's the code:

Code:
>=[Start Date] And <=[End Date]

The only issue is that, if users want all dates to populate, they leave the date fields empty and this causes no entries to show. Is it possible to alter this so that if these fields are left empty, all entries appear regardless of their dates?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:40
Joined
Feb 19, 2002
Messages
42,970
Code:
Where (SomeDate >=[Start Date] And SomeDate <=[End Date]) OR ([Start Date] Is Null)
 

bob fitz

AWF VIP
Local time
Today, 12:40
Joined
May 23, 2011
Messages
4,717
I have a date filtering criteria on a query where the user inputs the date period that they want to appear. Here's the code:

Code:
>=[Start Date] And <=[End Date]

The only issue is that, if users want all dates to populate, they leave the date fields empty and this causes no entries to show. Is it possible to alter this so that if these fields are left empty, all entries appear regardless of their dates?
Perhaps something like:
>=Nz([Start Date],"01/01/1001") And <=Nz([End Date],"01/12/3000")
 

oleronesoftwares

Passionate Learner
Local time
Today, 05:40
Joined
Sep 22, 2014
Messages
1,159
(Date >=[Start Date] And Date <=[End Date]) OR ([End Date]<=Date())
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:40
Joined
Feb 19, 2002
Messages
42,970
(Date >=[Start Date] And Date <=[End Date]) OR ([End Date]<=DateNow())
1. I hope the column name isn't actually Date since we all know the problems that causes.
2. You are comparing the prompt end date with an invalid function. I think you meant to use Now() to include the current time. But we have no way of knowing that the actual stored date won't be some date in the future which would mean that this suggestion couldn't work.

Near as I can tell, posting answers here is not a competition.
 

strive4peace

AWF VIP
Local time
Today, 07:40
Joined
Apr 3, 2020
Messages
1,003
hi @templeowls,

to allow for a time component, I like to do this:
SomeDate >=[StartDate] And SomeDate < ( [EndDate]+1 )

if you also want records to show with no date, you can do something like this:
SomeDate >= (nz([StartDate],0) And SomeDate < ( nz( [EndDate], #12/31/3021#) +1 )

where
if there's no start date, it will default to 12/31/1899
if there's no end date, it will default to 12/31/3021 -- just some big date ... I picked 100 years from the end of this month
 

strive4peace

AWF VIP
Local time
Today, 07:40
Joined
Apr 3, 2020
Messages
1,003
> "Why not use between?"

@Uncle Gizmo, for date ranges the way I suggested, the end date isn't actually included because it's everything less than a day later to accomodate a possible time part
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:40
Joined
May 7, 2009
Messages
19,169
also you can use the "Date" field if any of the date parameters are not filled in:

Code:
>=IIF(Len([Start Date] & "")=0, [yourDateField], [Start Date])
And <=IIF(Len([End Date] & "")=0, [yourDateField], [End Date])
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:40
Joined
Feb 19, 2002
Messages
42,970
You're going down a rabbit hole. The question was how to leave the date criteria blank and return all rows.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:40
Joined
Jul 9, 2003
Messages
16,244
Why not use between?

After reading Mike Wolfe's (member @NoLongerSet) latest blog:-

A Safer Alternative to BETWEEN When Filtering Dates​


I understand why you don't like using the "Between Statement". However, the problem isn't the "Between Statement" the problem is setting up the data in the first place! If it's a date, then make sure the data entry is date only! The problem is letting the time sneak in there!

Update:-
Another of Mikes blogs HERE with more detail:-

3 Gotchas of the SQL BETWEEN Statement​

 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:40
Joined
Jul 9, 2003
Messages
16,244
The Between Statement is much Safer
I had a customer who built a database in Excel and then transferred it into MS Access. For some reason the customer used the Creation date field of a record to link that record to other fields in other tables, using it like a unique ID. It was a couple of years ago now so I'm a bit rusty on exactly what happened, but my recollection is that the dates didn't match. The date and time for one record was exactly the same as another record, but they wouldn't appear in searches for a particular date/time. The actual physical look of the date, the text displaying the date and time, down to the the millisecond, were exactly the same. However, the underlying number representing the date and time was not exactly the same. So you had a fields with time and date exactly the same, but fundamentally at the mathematical level, it was not. Now this was a special case, but it makes me very wary of any date field which contains both date and time, particularly if you need to filter on it accurately... So in my book the Between Statement is much safer...
 

strive4peace

AWF VIP
Local time
Today, 07:40
Joined
Apr 3, 2020
Messages
1,003
sorry to disagree, @Uncle Gizmo

Dates are stored as double-precision numbers and are, therefore, inherently not accurate for exact comparison. Even though, when using BETWEEN, it's okay to specify the maximum first instead of the minimum.

Regardless of that, however, it's better to use >= or > AND <= or < due to faster performance, and it's not always desired to include the endpoints ~
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:40
Joined
Jul 9, 2003
Messages
16,244
Dates are stored as double-precision numbers and are, therefore, inherently not accurate for exact comparison

That's what I said......

I also agree that your formula is the best solution when the date has a time component...

The error is to allow the time component.
 

Users who are viewing this thread

Top Bottom