Changing Text to Date format in Access Query

[JustATheory]

Greetings,

I'm attempting to change a column from TEXT to a date format in a query. When I use Format([TEXT],"MM/DD/YYYY") it results the proper date format, but it's in text. I've tried Format([TEXT],#mm/dd/yyyy#) and receive an error. The goal is to have one query that will change a text format column to a date format without having to go into table design view and change the format.

Please help,

Thanks
Andy

 

[AlexCuse]

What format does is just that, format.

Access stores dates as integers, and displays them as dates. WIthout changing your date column to a Date/Time data type, you will not be able to do what you want.

What seems to be the problem though? Is it a sorting issue? If so, you can order your query by

Format([Text], "YYYYMMDD")

and it orders properly whether in Text or Date column.

Hope theis helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Remou]

How about CDate or DateSerial?

 

[PHV]

I'd use CVDate([TEXT]) (to handle Null valies properly).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886