Assistance with Date Format

[dhebert]

I am using an 2 text boxes as input for a date range. All dates in our database is a string: yyyymmdd (example - 20090101). I am using the following RunSQL:
DoCmd.RunSQL "SELECT ...
"INTO mtblIssueSummary " & _
"FROM ...
"WHERE KWODS_ODS_TIDPBOOK.TRANS_DATE Between (txtStartDate) and (txtEndDate) " & _

I would like to have the start date go back 180 days from the user input. Is this possible?

Thanks, Dave

 

[pbaldy]

Anything is possible. Because you store the date as a string, which I wouldn't have advised, I suspect you'll have to convert to a date (DateSerial and Mid functions), subtract the 180 days (DateAdd function) and convert it back to a string (Format or Year/Month/Day functions).

 

[dhebert]

Thanks Paul. I was hoping there was an easier way. Yes I'm running queries against our Enterprise database and I can't believe it was developed with the date range as a string. I am constantly using Format or Mid, Right, & Left functions to convert dates. It is a pain.
Thanks again for your help. I'll let you know how it worked tomorrow.

Dave

 

[pbaldy]

One thing you might consider is a query against the enterprise database that converts the string date to a real date, plus returns all the other fields. Then just base any other queries you run against that instead of the table itself. That way you don't have to worry about converting it any more. If the enterprise database is on SQL Server, I'd create a View that did that. Other systems may have similar capabilities.