Between Dates with Null values

[Danick]

I've searched this forum for this similar problem and found many similar posts. But none of them seem to solve my issue. So hopefully someone here has a simple solution.

I have a form with two text boxes called
txtFromDate
txtToDate

When the user enters a date in the text boxes, the query will find the dates between the two boxes. So in the query column of the field "Open" I entered,

Between [Forms]![frmSummary]![txtFromDate] And [Forms]![frmSummary]![txtToDate]

This works fine when the user enters a date in both boxes, but not when one or both is left blank.

Basically, what I would like to have happen is, show ALL records if both To and From boxes are left blank.

Or All records prior to the "To" date if just the txtToDate is filled
Or All records After the "From" date if just the txtFromDate is filled.

Is this possible from just a single query?

Thanks

 

[pbaldy]

Try the Nz() function:

Nz([Forms]![frmSummary]![txtFromDate], #1/1/1930#)

using dates that will be before/after anything in the data.

 

[Danick]

Try the Nz() function:

Nz([Forms]![frmSummary]![txtFromDate], #1/1/1930#)

using dates that will be before/after anything in the data.

Thanks!!

Here's the code that got it working.

Between NZ([Forms]![frmSummary]![txtFromDate],#1/1/1930#) And Nz([Forms]![frmSummary]![txtToDate],#1/1/2999#)

So far, this seems to be doing exactly what I wanted it to do.

Thanks again...

 

[pbaldy]

Happy to help!

 

[randallst]

I'm quite new to Access and this was one issue I have been struggling with for a while and I have just found the answer! Thank you guys, you're life savers!!

 

[pbaldy]

Excellent! Glad it helped you.