Optional Date Range Parameters

[tony_]

Hi,

I have a table with a date field (suppose invoice_date).
I want to see all invoices in a given date range.
Good so far, I can use BETWEEN.
What if I want to let users to be able to skip entering Start Date or End Date or even both? If Start Date isn't entered as a parameter and End Date is, I want to return all the records that are < to the End Date entered. Similarly, if End Date is not entered and Start Date is, I want to return all records that are > to the Start Date entered. If no date entered at all, I want to return every single record in the table.
I'm a noob in Access and I'd like to be able to do this using just Access Query Builder, not VB if possible of course.
Any ideas how this can be done?

Regards,
-Tony.

 

[vbaInet]

The idea is:

BETWEEN Nz([Start Date], DMin(...)) AND Nz([EndDate], DMax(...))

DMin() and DMax() will be the alternative dates used if either Start Date or End Date is ommitted. DMin will return the minimum date in the table and DMax will return the maximum date in the table. Fill in the blanks.

 

[tony_]

The idea is:

BETWEEN Nz([Start Date], DMin(...)) AND Nz([EndDate], DMax(...))

DMin() and DMax() will be the alternative dates used if either Start Date or End Date is ommitted. DMin will return the minimum date in the table and DMax will return the maximum date in the table. Fill in the blanks.

That's awesome.
Now, I'll throw a wrench into this. What if I had another field (say customer_id) and I need to have a parameter on this field in the same query and this should also be optional?
Say if customer_id and start_date are entered I want the result set to have all records with this customer_id after the start_date entered. Or if customer_id is not entered and end_date is, I want all records before the date entered to be returned regardless of customer_id.
Is this possible?

 

[vbaInet]

The best way is to get the parameters via a form, then you build your sql in code. That's how I would do it but you obviously need to know how to code

Nevertheless,

[Please Enter Customer ID] OR [Please Enter Customer ID] Is Null

 

[tony_]

The best way is to get the parameters via a form, then you build your sql in code. That's how I would do it but you obviously need to know how to code

Nevertheless,

[Please Enter Customer ID] OR [Please Enter Customer ID] Is Null

I know how to code, just not in SQL.

I did try this before but what ends up happening is Access creates another column in the query and even if I do enter customer_id, it returns all of the records withing the date range I entered.

 

[vbaInet]

You put the criteria under the Customer_ID field and ensure that the it's not the same name as the field.

 

[gemma-the-husky]

to get all dates, i would just set one date to zero, and the other to datevalue #13/31/2099# (say)

then your between date1 and date2 will still work.

 

[tony_]

You put the criteria under the Customer_ID field and ensure that the it's not the same name as the field.

Damn, I thought this was what I did, but I wiped it out and did it again and it worked this time

Thanks a bunch!!!

 

[tony_]

to get all dates, i would just set one date to zero, and the other to datevalue #13/31/2099# (say)

then your between date1 and date2 will still work.

This makes sense, but the data will not be kosher this way. If for example amount is a required field then you're forced to create 2 extra transactions with amount that's <> 0. I know, you can put $0.01 in the amount field for both of them and it won't cause any trouble but ... not kosher nonetheless. Also, if you ever need # of transactions, you always have to remember to subtract 2 from the result. Then, there's a customer_id to worry about, so you'll most likely have to create a dummy customer and then exclude him from all the queries on both the transaction and customers tables.
If there's a viable solution that doesn't involve adjusting your source data, I prefer to go that route.

 

[vbaInet]

You're welcome!

By the way, gemma-the-husky makes a good point. If you use a date like he mentioned in the DMax() part and for the DMin() part you can use a date in 1901 or something similar.