Prompt user for query date range

[Sade]

Hello all. I an trying to write what should be a simple query to pull data within a date range specified by the user. This should be easy enough with the bracketed prompt in the criteria.

However, the query uses the "DateSerial" function to change the format of the field to date formatted instead of text. During my filter tests, just typing in a filter of "05/01/2011" does not work. I have to manually put in the number signs to signify a date: "#05/01/2011#". Usually this happens automatically, but I guess Access doesn't recognize the DateSerial format until after the query runs.

So, is there a way to prompt the user for date input and have Access put the "#" in automatically? I suppose I could do two queries so that Access would recognize the field as a date, but I'm looking for efficiency here.

Thanx

 

[Montez659]

If I understand what you are wanting correctly, simply creating a form with two text boxes to choose a date range should suffice. For each text box, set the Format property to Short Date (or long date if you prefer). Make sure, "Show Date Picker" is set to "For Dates" or Always if that is an option.

This forces the user to enter a valid date which Access will recognize correctly. You will need to change the formula in your query to something like:

>=[Forms]![yourFormNameHere]![txtBegDate] And <=[Forms]![YourFormNameHere]![txtEndDate]

for whatever field the date is held. Also, change txtBegDate and txtEndDate to names of your text boxes on your form.

Then add a command button to run the query, and you should be set. Make sure that the query is run before the form closes, or else you will hit an error.

 

[Sade]

Perfect ... Thanx for the help!