Date criteria on query if left blank

[gojets1721]

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:

>=[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]

Where (SomeDate >=[Start Date] And SomeDate <=[End Date]) OR ([Start Date] Is Null)

 

[bob fitz]

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:

>=[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]

(Date >=[Start Date] And Date <=[End Date]) OR ([End Date]<=Date())

 

[Pat Hartman]

(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]

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

 

[Uncle Gizmo]

Why not use between?

 

[strive4peace]

> "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

 

[arnelgp]

also you can use the "Date" field if any of the date parameters are not filled in:

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

 

[Pat Hartman]

You're going down a rabbit hole. The question was how to leave the date criteria blank and return all rows.

 

[Uncle Gizmo]

Why not use between?

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

A Safer Alternative to BETWEEN When Filtering Dates​

A Safer Alternative to BETWEEN When Filtering Dates

This convenient function generates defensive SQL statements to help you avoid the subtle dangers of the BETWEEN statement when filtering dates.

nolongerset.com

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​

3 Gotchas of the SQL BETWEEN Statement

The SQL BETWEEN statement produces very readable condition expressions. But developers of all skill levels can still get into trouble with it.

nolongerset.com

 

[Uncle Gizmo]

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]

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 ~

 

[Uncle Gizmo]

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.