Access Date Problem

[md092686]

I have a field with dates that are Text 10012007. I want to place a slash between the date as: 10/01/2007. Can an update procedure do this in a query?

 

[GingerR]

Yes. Look up Mid(), Left() and Right() functions.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dinger2121]

Are your months always going to be in format of 01 (as opposed to just 1)? In that case the following will work -

Left([dateField],2) & "/" & Mid([dateField],3,2) & "/" & Right([dateField],4)

 

[GingerR]

If you want that field to act like a real "date", you'd have to make another field in your table that is a DATE type. Make the update query put the new data into that field. Also might have to wrap it in CDate(blah) to convert it to a date.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[SkipVought]

BTW, the TEXT, 10/01/2007, is ambiguous.

Is it mm/dd/yyyy or is it dd/mm/yyyy?

It is good to use the TEXT in the form yyyy/mm/dd, which is UNAMBIGUOUS.

But you really do not want to ultimatly use TEXT in a table. You want Real Dates. So either force conversion using the # delimiter, like...

MyDate: #2007/10/01#

or

MyDate: DateSerial(2007,10,01)

and if your DateText is 10012007, then the conversion would be EITHER

MyDate: DateSerial(Right([DateTEXT],4),Left([DateTEXT],2),Mid([DateTEXT],3,2))

OR

MyDate: DateSerial(Right([DateTEXT],4),Mid([DateTEXT],3,2),Left([DateTEXT],2))

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]