Linked table, Convert to date

[dhborchardt]

I have a linked table that has dates given in data type "text" instead of data type "date". I have a query in which I need to sample the dates using the criteria "Between (Date()-8) And Date()". This works on data type date but not text. I get the error "data type mismatch...." How do I convert the data types from text to date in the query? Do I just enter the expression in the field space on the query, or do I call a proceedure.

Thanks
Dale

 

[LearningSql]

Hi Dale --

I believe the Function CDate is what you are looking for.
In the example below, a field named TEXT_DATE contains text data. The function below converts the TEXT_DATE to a datetime format and then subtracts it from the current date.
Note that I have not tried the function with a "NULL" value so you may have to check for invalid dates.

Date() - CDate([date_text])

Hope this helps.
Pru