My date field is a string, in the MMDDYY format eg: 012904

I want to write a query to pull all the records with date greater than '01/29/04'

I have oracle background and there were nice functions like TO_DATE(columnname,'MM/DD/YY') to use in my query and I am wondering if there is anything like this in MS Access. If not, how do I solve this?

Thanks
Shanti

I would urge you to change the datatype of your date field FROM string TO date in the first place. You'll avoid all these problems in the future.

As for dates already in your system as text fields, you can do this:

dtmNewDate = DateSerial(Right(strOldDate, 2), Left(strOldate, 2), _
   Mid(strOldate, 3, 2))


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks! It works!

I have not designed the database, someone else created the field as text type instead of date. I don't think they want to change that now.

Thanks for your help.
Shanti