Automatically set the data type as date for a Make Table Query

G

Guest

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

Douglas J. Steele

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

Guest

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
 
G

Guest

CDate worked thanks for the help.

Douglas J. Steele said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top