Format Date Problem

[amal1973]

I have a date column in table that was imported as text Ex: 20020705. That reads the fifth of July 2002. I am tiring to have it look like this format in the column. 05/07/2002. JI have tried Cdate, DateSerial. But with no luck L appreciate any help.

 

[RobBroekhuis]

If they are always 8 digits, then you should be able to use something like:

strDate=range("A5&quot 'to convert the date in A5
iDay=val(mid(strDate,7))
iMon=val(mid(strDate,5,2))
iYear=val(left(strDate,4))
range("A5&quot=dateserial(iYear,iMon,iDay)
range("A5&quot.numberformat="dd/mm/yyyy"

You can put this code inside a loop that tackles all of your afflicted cells one by one.
Rob

 

[amal1973]

thanks .. could you tell me how can i do it in a record set. i am using access 2000 .thanks

 

[Automaton]

Try an update query... I created a small database with five records in one column and an empty column waiting for the converted output. ImportData was the column with the data and ConvertData was the empty 'receptacle'. On the Query tab I designed a new query, added my table, clicked on both fields to add them to the design grid, then selected "UPDATE QUERY" from the Query menu item. On the third line of the design grid under ConvertData I typed:

=Mid([importdata],5,2) & "/" & Right([importdata],2) & "/" & Left([importdata],4)

When I ran the query it converted entries like 19990807 under ImportData to 08/07/1999 under ConvertData.