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?

 

[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())

 

[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

 

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

 

[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 ~