Change Date properties during Make Table Qry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using a Make table qry to "localize" data that I'm extracting from on
ODBC connection. The field in the linked database is a text field, but it is
now storing dates. Without changing the systems defaults is there a way to
have that stored as a date field in my database. I saw a prior post about
using the CDATE ( ) function but because some of the data is in the system in
format MM/DD/YYYY and others as MMDDYYYY it gives weird results for the
MMDDYYYY format. If I manually go to the table design and change it from
Text to Date/Time I get the results I'm looking for (which is to blank out
the MMDDYYYY and change the MM/DD/YYYY to date time). Since we update daily
data I'm trying to find a way to automate this with a macro or through the
qry design.
 
Try this

select cvdate(iif(instr(FieldName ,"/")=false,format(FieldName
,"00/00/0000"),FieldName)) as DateField From TableName
 
Worked like a champ
Thanks

Ofer said:
Try this

select cvdate(iif(instr(FieldName ,"/")=false,format(FieldName
,"00/00/0000"),FieldName)) as DateField From TableName
Click to expand...
 
Back
Top