change text format in one column to date format in another

P

PaulG

I have a column of dates fomated as text. They appear as yyyymmdd. When I
try to reformat as the column as date, Access says it can't do it. How can I
reformat the column so that the dates appear as yymmdd i.e. 041021

P
 
J

John Vinson

I have a column of dates fomated as text. They appear as yyyymmdd. When I
try to reformat as the column as date, Access says it can't do it. How can I
reformat the column so that the dates appear as yymmdd i.e. 041021

P
Click to expand...

This isn't a "format" issue - it's a datatype issue. A Text field and
a Date/Time field are two different datatypes, stored in different
ways.

I would suggest that you open your table in design view and add a
*new* field, using the Date/Time datatype. Then run an Update query
updating this field to

CDate(Mid([textdate], 5, 2) & "/" & Right([textdate], 2) & "/" &
Left([textdate], 4)

This will load the field with an actual numeric date/time value. You
can set the Format property of this field to yymmdd (or to any other
valid date format).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top