expressions

R

Renetta

I need to change a format and am not quite sure about how to write the
expression. The data was pulled into access from several like excel table,
hence the formatting is a bit off.

Currently my dates are reflecting date as "20061231" and is set as text. I
need it to reflect as a true date 12312006 for querying purposes.
 
J

John W. Vinson

I need to change a format and am not quite sure about how to write the
expression. The data was pulled into access from several like excel table,
hence the formatting is a bit off.

Currently my dates are reflecting date as "20061231" and is set as text. I
need it to reflect as a true date 12312006 for querying purposes.
Click to expand...

20061231 is actually a (marginally) better way than 12312006 for storing date
data in a Text field, as it will sort chronologically.

Better than either is to store it in a Date/Time field. Consider adding a new
field to your table; specify it as Date/Time datatype. Run an Update query
updating it to

DateSerial(Left([XLDate], 4) , Mid([XLDate], 5, 2), Right ([XLDate], 2))

where XLDate is the name of your imported date field.

This Date field can be formatted any way you like - "mmddyyyy" to appear as
12312006, "mm/dd/yyyy" as 12/31/2006, "mmmm dd, yyyy" as December 31, 2006 and
so on.

John W. Vinson [MVP]
 
Top