Use a form to filter dates on a query, or leave blank to show all dates

[ObiBenKenobi]

Apologies if this is a duplicate thread.

I have a form where I would like users to filter a date range in a query.

Ideally, the users would be able to put a date in either, neither, or both of the designated text boxes to apply the proper date filter to the query.

I can reference form fields in my query easy enough, but my problem is that I don't know how to tell the query to show me everything if one or both of the text box fields are left blank. My problem is compounded because the query itself appropriately contains null values in the date column.

Here's what I want to say (and have tried countless times with nested IIfs, Betweens and a combination thereof):

(txtStartDate and txtEndDate are the text box fields on the form)

If txtStartDate and txtEndDate are null, don't apply any filter
If txtStartDate and txtEndDate are not null, filter between those two
If txtSartDate is null and txtEndDate is not null, filter from the beginning of time to txtEndDate, while showing no nulls
If txtStartDate is not null and txtEndDate is null, filter from txtStartDate to the end of time, while showing no nulls

I don't know if I'm foolish for trying this in context of the query itself or if I should write some VBA to apply the filters.

Any help would be appriciated!

 

[spikepl]

use NZ-function (look it up) in the query for the criteria. If the criterion is null, supply NZ a value which is way off, eg 1/1/1900 for one end and 1/1/2100 for the second end.

 

[ObiBenKenobi]

Hi Spikepl, Thank you so much for your response.

I've semi-successfully used the Nz function, but my problem is that when both fields are left blank, the records with null date fields don't show up.

Here's the code I placed in the criteria line of my query:

Between Nz([forms]![frm_Main_Form].[txtStartDate],#1/1/1900#) And Nz([forms]![frm_Main_Form].[txtEndDate],#1/1/2100#)

Is there a way to modify this to include nulls when both are blank?

 

[spikepl]

try this:

Add one more criterion:

OR ( [YourFieldWithPosisibleNullAsDate]+ [forms]![frm_Main_Form].[txtStartDate] + [forms]![frm_Main_Form].[txtEndDate]) IS NULL

Oops -that won't work. But this might:

OR ( Nz([YourFieldWithPosisibleNullAsDate],0)+ Nz([forms]![frm_Main_Form]![txtStartDate] ,0).+ Nz([forms]![frm_Main_Form].[txtEndDate],0)) =0

 

[ObiBenKenobi]

HOO-Rah!

Thank you so much! Adding your second line to my criteria worked perfectly.

 

[vbaInet]

Hopefully you would have upgraded your criteria before you reach the year 2100

 

[ObiBenKenobi]

I put a reminder for myself on my Outlook calendar.

 

[vbaInet]

Don't forget to put it in your will too, just in case

 

[kheekhay15]

This is great. I've been looking for this solution for days! Thank you!

 

[Amnross]

try this:

Add one more criterion:

OR ( [YourFieldWithPosisibleNullAsDate]+ [forms]![frm_Main_Form].[txtStartDate] + [forms]![frm_Main_Form].[txtEndDate]) IS NULL

Oops -that won't work. But this might:

OR ( Nz([YourFieldWithPosisibleNullAsDate],0)+ Nz([forms]![frm_Main_Form]![txtStartDate] ,0).+ Nz([forms]![frm_Main_Form].[txtEndDate],0)) =0

I ran across this post and it would seem to be the closest that I have found to helping me. However, I'm still having some problems.

In my query "criteria" line, I put:
Between Nz([Forms]![IPR Search Form]![Test Review From],#1/1/2013#) And Nz([Forms]![IPR Search Form]![Test Review To],(Date()+1))

and in the query "or" line, I put:
(Nz([IPR]![Test Review Date],0)+Nz([Forms]![IPR Search Form]![Test Review From],0)+Nz([Forms]![IPR Search Form]![Test Review To],0))=0

When I try to run the query I get the following error:
This expression is typed incorrectly, or it is too complex to be evaluated.

While this seemed to work for ObiBenKenobi, I am struggling with it. :banghead:

"IPR" is the working table
"Test Review From/To" are text boxes that have defaulted values of 1/1/2013 and Date() respectively.

Because "Test" may not have been assigned yet, these inputs will be blank in the table. However, I still want them to show up on a "between" date search.

Can anyone help?