Update Query

[Guest]

I want to create an expression for an update query . In the "Current" table,
there is a date field. It is a 6 character field called "Date". The first 2
characters are the month, the second 2 characters are the day and the third 2
characters are the year.

I would like to convert the dates as follows into a new field called "Good
Date":

110199.........11/01/99
021505.........02/15/05
120606.........12/06/06

I would like to run an update query after I import the data into the
"Current" table. The data is in the "Date" field and I would like to update
the "Good Date" field on each record.

I'm having trouble getting started with the syntax for an expression that
will do this for me. Thanks for the help....

 

[Douglas J. Steele]

Try:

UPDATE MyTable
SET [Good Date] = CDate(Format([Date], "00/00/00"))

Note that this will only work if the user's Regional Settings have set the
Short Date format to mm/dd/yyyy. To handle other default Short Date formats,
it's probably better to use:

UPDATE MyTable
SET [Good Date] = DateSerial(Right([Date], 2), Left([Date], 2), Mid([Date],
3, 2))

And just a comment: you should avoid using Date as a field name. Date is a
reserved word, and using reserved words for your own purposes can lead to
problems.