microsoft.public.access

This is the formula I am using. I was hoping the Format ( ) function would
solve my issue.

Date: Format(Mid([sd_datekey],5,2) & "/" & Mid([sd_datekey],7,2) & "/" &
Mid([sd_datekey],3,2),"mm/dd/yy")

The raw data in [sd_datekey] looks like this: 20071021 15:23:22.810

"Douglas J. Steele" wrote:

> How are you getting the date into mm/dd/yy format? If all you're doing is
> string manipulation, Access has no way of knowing that it's supposed to be a
> date. Try wrapping the CDate function around the string to see whether
> coercing it into a date solves your problem.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "WildlyHarry" wrote in message
> news:04A55EF8-941C-4522-B940-64512E46BC3B@microsoft.com...
> >I have a make table query that I run daily. One of the fields of the query
> > is a date field. The source table for the query treats the information as
> > a
> > text string in a date format that is not recognized by access. I have
> > created formula that parses the string to put it into standard "mm/dd/yy"
> > format. However, when I run the make table query it treats the date field
> > as
> > text. This is a problem because I use a form to hold the criteria for a
> > query that I run on the new table. In order to get the query to work, I
> > have
> > to manually set the data type to short date. This will not work at the
> > user
> > level, because most of them have no clue how to set a data type. Is there
> > anyway to force access to recognize my date field as a short date data
> > type?
> > Thanks in advance for the help.
>
>
>