query filter based on dates - excludes null values

[krowe]

Hi

I have been researching how to return all values in a query when a form critieria is left blank. I have made some progress, the combo box criteria queries were fairly simple, but i'm getting stuck with my date criteria. My query doesn't return null values when I want it to.

I want it to return all records (including null values) if the form OpenFrom and OpenTo dates are blank, and just the values between the selected dates (excluding null values) if the form is completed.

Here is my query

SELECT qryReportSelector2_Authority.*, qryReportSelector2_Authority.ApplicationDate AS ApplicationDateFilter
FROM qryReportSelector2_Authority
WHERE (((qryReportSelector2_Authority.ApplicationDate) Between Nz([Forms]![frmReportSelector]![OpenFromDate],DMin("[ApplicationDate]","[qryAllCases]")) And Nz([Forms]![frmReportSelector]![OpenToDate],DMax("[ApplicationDate]","[qryAllCases]"))));

Please can someone help me sort this one out?

Thanks

Kev

 

[CazB]

WHERE (((qryReportSelector2_Authority.ApplicationDate) Between Nz([Forms]![frmReportSelector]![OpenFromDate],DMin("[ApplicationDate]","[qryAllCases]")) And Nz([Forms]![frmReportSelector]![OpenToDate],DMax("[ApplicationDate]","[qryAllCases]"))));

You could try adding

or isnull(([Forms]![frmReportSelector]![OpenFromDate]))

into your criteria? And the same again for the OpenTo Date...

 

[krowe]

Hi

Thanks for your reply, sorry i have tried i keep getting syntax errors, please could you be a bit more specific how it fits in with my existing query?

thanks

Kev

 

[CazB]

OK, a sample WHERE from my own database that does something similar, adapted to your field names

WHERE (((IIf(Not IsNull([Forms]![frmReportSelector]![OpenFromDate],qryReportSelector2_Authority.ApplicationDate>=[Forms]![frmReportSelector]![OpenFromDate],-1))=True) AND ((IIf(Not IsNull([Forms]![frmReportSelector]![OpenToDate]),qryReportSelector2_Authority.ApplicationDate<=[Forms]![frmReportSelector]![OpenToDate],-1))=True))

 

[krowe]

Hi

Thanks

But it says there is the wrong number of arguments, I would try and troubleshoot it but I really dont understand how it works

Ill try and strip out the data and post the db here if that ok?


Kev

 

[krowe]

Hi here is the db, the query I am struggling with is called qryReportSelector3_ApplicationDate.

Thanks

Kev

 

[krowe]

Hi

I've had an idea earlier and come up with this, not the same as what has been suggested but seems to work. Is there a limitation (beyond the database being in use after 2099!) that I am missing here:

Between (IIf(IsNull([Forms]![frmReportSelector]![OpenFromDate]),#01/01/1901#,[Forms]![frmReportSelector]![OpenFromDate])) And (IIf(IsNull([Forms]![frmReportSelector]![OpenToDate]),#31/12/2099#,[Forms]![frmReportSelector]![OpenToDate]))

Thanks

ok, i was a bit hastie, that doesnt return the null values when the field is left blank

...
Kev

 

[namliam]

it will if you use the NZ function on the column that is getting the between...


The best solution if you want to addapt your query to work properly dynamicaly... is to addapt it dynamicaly...
It is something called "Query by form"
psuedo code

mysql = "Select ... from ..." 
mywhere = "Where 1=1"
if not isnull ( yourfield)  then
mywhere = where & " AND .... some where clause here " 
endif

currentdb.querydefs("YourQUery").sql = Mysql & mywhere

THen execute your query.

 

[krowe]

how about this:

([ApplicationDate]>=[Forms]![frmReportSelector]![OpenFromDate] Or [Forms]![frmReportSelector]![OpenFromDate] Is Null) And ([ApplicationDate]<=[Forms]![frmReportSelector]![OpenToDate] Or [Forms]![frmReportSelector]![OpenToDate] Is Null)