Query Access with user defined date range

[Guest]

I am writing a macro to run a query on an Access DB and want the user to input a date range to filter the query. I am using an input box, but if the user doesn't type the date in the correct format "yyyy-mm-dd" then I recieve an error. Here is the code I have so far

daterng = Application.InputBox(prompt:="Enter the Starting date you wish to calculate:"
, Title:="Week Ending Date"

daterng2 = daterng - 180 'I want the data displayed to be a date range of approx 6 month

...."AND (Billing.ST_DATE>={ts '" & daterng & " 00:00:00'} And Billing.ST_DATE<={ts '" & daterng2 &

The problem is that daterng and daterng2 have to be a "Dim daterng as String" and cannot be "as Date" otherwise the query macro will error. If daterng is a string then I cannot perform the "daterng-180" calculation. Also, daterng has to be in the format of "yyyy-mm-dd"

I currenty have two input boxes for each daterng and just hope that the user inputs the date in the correct format. But I'd like to autmate the second daterng and eliminate the need for the correct formatting and have the macro reformat it correctly and then convert it to a string. Thanks for your help

 

[Kalle]

CorrectFormatIs = Format( YourValueFromInputBox , "yyyy-mm-dd")

 

[Tom Ogilvy]

You need to validate the users entry and insure it is in the right format,
or reformat it.

? format("4/22/2004","yyyy-mm-dd")
2004-04-22

so something like
if isdate(daterng) then
daterng = format(daterng,"yyyy-mm-dd")


--
Regards,
Tom Ogilvy

I am writing a macro to run a query on an Access DB and want the user to

input a date range to filter the query. I am using an input box, but if the
user doesn't type the date in the correct format "yyyy-mm-dd" then I recieve
an error. Here is the code I have so far:

daterng = Application.InputBox(prompt:="Enter the Starting date you wish to calculate:" _
, Title:="Week Ending Date")

daterng2 = daterng - 180 'I want the data displayed to be a date range of approx 6 months

..."AND (Billing.ST_DATE>={ts '" & daterng & " 00:00:00'} And

Billing.ST_DATE<={ts '" & daterng2 & "

The problem is that daterng and daterng2 have to be a "Dim daterng as

String" and cannot be "as Date" otherwise the query macro will error. If
daterng is a string then I cannot perform the "daterng-180" calculation.
Also, daterng has to be in the format of "yyyy-mm-dd".

I currenty have two input boxes for each daterng and just hope that the

user inputs the date in the correct format. But I'd like to autmate the
second daterng and eliminate the need for the correct formatting and have
the macro reformat it correctly and then convert it to a string. Thanks for
your help.