Help Needed Please: Date Parameter in Query - Leaving Blank to Include all Records??

[phil5556]

Hi

Bit of an Access beginner and am trying to sort something out for work - not sure why they've asked me!

I've created a query to search on a couple of items using drop down boxes on a search form I created. This bit of it works fine, I used this site http://www.fontstuff.com/access/acctut08.htm and copied what he had done. This is fine.

I now want to add a date search to the same query. I know I can use Between [..] AND [..] but if I leave the boxes blank it finds no records. I'd like it to search and include all.
Ideally I want to include 2 extra text boxes on my form that I can put a to and from date in (or not put a date in and it find everything).

Hope that makes sense, please can someone do me an idiots guide?

Many thanks
Phil

 

[ajetrumpet]

Ideally I want to include 2 extra text boxes on my form that I can put a to and from date in (or not put a date in and it find everything).

Look at this example database....the SQL in the "Late Payments" query does the same thing you are asking about.

Also, the "Nz" function accomplishes the same thing.

 

[phil5556]

Cheers for that, it's nearly what I'm after apart from the fact it only picks out records where the Voucher Date is Greater than the Due Date. And I'm not sure how to adapt it to make it pick all records between the chosen dates.

Thanks for your help.

 

[Jon K]

See the form and query in the attached sample database.

For explanation, you can take a look at this thread and its Notes:
http://www.access-programmers.co.uk/forums/showthread.php?t=103312
.

 

[phil5556]

cheers mate, that's exactly what I'm after. All working perfectly! Seems a very long winded way around something so simple! I can get on with something else and stop throwing things at the computer now

 

[ajetrumpet]

Seems a very long winded way around something so simple!

Welcome to the computer work Phil....one thing at a time for the machine....it's not long winded, it's step-by-step