using query to format date

[Larry Rekow]

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."

[Douglas J. Steele]

Re: using query to format date

If the date value is in a text field, you're going to have to convert it to
a date before you can reformat it.

Unfortunately, Access isn't going to recognize 20040511 as a date: you're
going to have to break it into its component parts:

Format(DateSeri al(Left(TextDat e, 4), Mid(TextDate, 5, 2), Right(TextDate,
2)), "m/d/yy")

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(No private e-mails, please)



"Larry Rekow" <larry@netgeexd otcom> wrote in message
news:9le2a0d19s cf9ngttknrllmvb snvqrnvvk@4ax.c om...[color=blue]
> 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."[/color]

[Larry Rekow]

Re: using query to format date

Many thanks,

Larry Rekow

On Tue, 11 May 2004 23:34:29 GMT, "Douglas J. Steele"
<NOSPAM_djsteel e@NOSPAM_canada .com> wrote:
[color=blue]
>If the date value is in a text field, you're going to have to convert it to
>a date before you can reformat it.
>
>Unfortunatel y, Access isn't going to recognize 20040511 as a date: you're
>going to have to break it into its component parts:
>
>Format(DateSer ial(Left(TextDa te, 4), Mid(TextDate, 5, 2), Right(TextDate,
>2)), "m/d/yy")[/color]

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