Conversion of Date

[Luhrern]

I have a database where dates is pesented by a 8 position text string i.e. 20020726. What kind (if any) of conversion function in Access can I use to convert this to a date i.e from 20020726 (8 position date format) to 2002.07.26 ?
Regards

Luhrern

 

[cew657]

I would create a new field in your table called NewDate. Then I would create an Update Query and place this new field in the Field grid and Update it to:

Left([your field name],4) & "." & Mid([your field name],5,2) & "." & Right([your field name],2)

I don't like updating my fields and this is the reason I would create a new column to update.

cew657

 

[Junior1544]

i like the idea of a seperate field... but i would suggest looking into the format command in vba... it might be an easier way of doing what you are looking at...


--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Robway]

I've been wanting to know how to do this for ages. Thanks.

Could you also use that code to show the date on a report, so that you don't have to change the original data?

Thanks, Robyn

 

[cew657]

If you wanted to use this in a report, you could create a text box and place the formula in it. When you ran the report, the date would show up like 2002.07.26

HTH

cew657

 

[SgtJarrow]

Just to add my $.02

I have an app where it was much more effieict for storing data and sorting to use a date format of text strings like 200206. This is because all data entered in one month was the same, so adding it all up later is much easier. But the users did not want to see that date like that....they wanted to see January 2002. So I created a combo box and used the following to make change:

Format(DateSerial(Left([Date],4),Right([Date],2),1),"mmmm yyyy&quot

Hope this helps. The secret to creativity is knowing how to hide your sources. - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer