I have a text table i'm linking to in an access d/b. the date is
displayed as 20040511
for May 11, 2004

I'd like to use an expression in a query to display this as 5/11/04

but after some research and googling i'm coming up empty.

anyone have the answer to this and perhaps a page of date examples?

thanks.
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

Larry Rekow wrote:
"stringdate", you can use the following SQL to understand the normal how
you can make the normal date:

SELECT stringdate, Left([stringdate],4) AS year, Mid([stringdate],5,2)
AS Month, Mid([stringdate],7,2) AS day,
DateSerial(Left([stringdate],4),Mid([stringdate],5,2),Mid([stringdate],7,2))
AS normalDate
FROM Table1;

If you want to make it in code instead of SQL you can do the following:
stringdate = "20040511"
normaldate=
DateSerial(Left(stringdate,4),Mid(stringdate,5,2), Mid(stringdate,7,2)


Hope this helped.
Tell us how it worked for you.

Larry Rekow <larry@netgeexdotcom> wrote

Hello Larry,

Look up the Format function in Access help.

Example: Format(MyDate, "mmm d yyyy")' Returns "Jan 27 1993".

A quick note: you might have to split out the data in the field
and combine it back together so that you will display it in the
correct format.

Add these fields into your source query and replace "MyDate"
field with the one your using.

YearDate: Mid([MyDate],1,4)

MonthDate: Mid([MyDate],5,2)

DayDate: Mid([MyDate],7,2)

DateFormated: CDate([MonthDate] & [DayDate] & [YearDate])

NewDateFormat: Format(DateFormated, "mmm d yyyy")

Hope this starts you in the right direction.

Regards,

Ray