convert text "mmddyy" to sortable date format

[Guest]

I have Access tables (office 2K3) with a column (Text data type) that lists
dates as mmddyy. I would like them to be of the Date/Time data type so that
I can sort the records based on date. I do not care what form (i.e.
mm/dd/yyyy, etc.) that they end up in. Any idea how I could do this. Thanks,

S Taylor
NCSU

 

[Douglas J. Steele]

CDate(Format([TextDate], "##\/##\/\2\0##"))

This assumes they're all years 2000 and later. If not, leave out the \2\0
and see whether it interprets the year correctly.

 

[Mike Labosh]

I have Access tables (office 2K3) with a column (Text data type) that lists

dates as mmddyy. I would like them to be of the Date/Time data type so
that
I can sort the records based on date. I do not care what form (i.e.
mm/dd/yyyy, etc.) that they end up in. Any idea how I could do this.
Thanks,

The DateSerial function takes three strings for year, month and day, and
returns a real live date/time data type. I have not used Access 2003, but I
hear it has a lot of really paranoid security features that holler at you if
you try to run code like my example below, but that's a different issue.
Probably a checkbox in the options dialog.

If your text data is formatted like mmddyy, you can do this to convert it:

1. Add a new Date/Time type column to your table
2. Use an Update query like this one: [air-code]

UPDATE YourTable
SET NewDateCol =
DateSerial(
Right$(OldDateCol, 2),
Left$(OldDateCol, 2),
Mid$(OldDateCol, 3, 2)
)

3. Optionally delete the old column from your table

--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"

 

[Guest]

I appreciate your response.

I attempted an update query (as I saw you advise in a similar post) as
follows: I created a new field, formatted it as Date/Time, entered the CDate
function in the "Update to:" row for the new field, and "Is Not Null" in the
"Criteria:" row for the original text field.

This did not work. Access said that it did not return values for some
records due to a type conversion error.

There are dates that are pre 2000, so I tried both ways (i.e. with and
without the \2\0).

Is the update query not the correct means of executing the CDate function?

Thanks,
S Taylor
NCSU

CDate(Format([TextDate], "##\/##\/\2\0##"))

This assumes they're all years 2000 and later. If not, leave out the \2\0
and see whether it interprets the year correctly.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have Access tables (office 2K3) with a column (Text data type) that lists
dates as mmddyy. I would like them to be of the Date/Time data type so that
I can sort the records based on date. I do not care what form (i.e.
mm/dd/yyyy, etc.) that they end up in. Any idea how I could do this. Thanks,

S Taylor
NCSU

 

[Guest]

I appreciate your response as well.

The update query worked except that the original format of mmddyy does not
have a zero character holder for the months of january through september.
thus, 10100 is returned as 10/10/2000 as opposed to 01/01/2000.

I understand why this happens, but I do not know how to fix it as it appears
that you would need an If statement within the DateSerial function.

Again, thanks for your help.

S Taylor
NCSU

I have Access tables (office 2K3) with a column (Text data type) that lists
dates as mmddyy. I would like them to be of the Date/Time data type so
that
I can sort the records based on date. I do not care what form (i.e.
mm/dd/yyyy, etc.) that they end up in. Any idea how I could do this.
Thanks,

The DateSerial function takes three strings for year, month and day, and
returns a real live date/time data type. I have not used Access 2003, but I
hear it has a lot of really paranoid security features that holler at you if
you try to run code like my example below, but that's a different issue.
Probably a checkbox in the options dialog.

If your text data is formatted like mmddyy, you can do this to convert it:

1. Add a new Date/Time type column to your table
2. Use an Update query like this one: [air-code]

UPDATE YourTable
SET NewDateCol =
DateSerial(
Right$(OldDateCol, 2),
Left$(OldDateCol, 2),
Mid$(OldDateCol, 3, 2)
)

3. Optionally delete the old column from your table

--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"

 

[Douglas J. Steele]

Sounds as though some of the fields don't have valid dates in them.

Try a Select query with a computed field IsDate(Format([TextDate],
"##\/##\/##")), with False as the criteria (continue with the Is Not Null as
the criteria for the date field)

That should return all of the rows that are causing problems.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I appreciate your response.

I attempted an update query (as I saw you advise in a similar post) as
follows: I created a new field, formatted it as Date/Time, entered the CDate
function in the "Update to:" row for the new field, and "Is Not Null" in the
"Criteria:" row for the original text field.

This did not work. Access said that it did not return values for some
records due to a type conversion error.

There are dates that are pre 2000, so I tried both ways (i.e. with and
without the \2\0).

Is the update query not the correct means of executing the CDate function?

Thanks,
S Taylor
NCSU

CDate(Format([TextDate], "##\/##\/\2\0##"))

This assumes they're all years 2000 and later. If not, leave out the \2\0
and see whether it interprets the year correctly.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have Access tables (office 2K3) with a column (Text data type) that lists
dates as mmddyy. I would like them to be of the Date/Time data type

so
that

I can sort the records based on date. I do not care what form (i.e.
mm/dd/yyyy, etc.) that they end up in. Any idea how I could do this. Thanks,

S Taylor
NCSU

 

[Mike Labosh]

The update query worked except that the original format of mmddyy does not

have a zero character holder for the months of january through september.
thus, 10100 is returned as 10/10/2000 as opposed to 01/01/2000.

I understand why this happens, but I do not know how to fix it as it
appears
that you would need an If statement within the DateSerial function.

UPDATE YourTable
SET NewDateCol =
DateSerial(
Right$(OldDateCol, 2),

--> Left$(Iif(Len(OldDateCol) = 5, "0" & OldDateCol, OldDateCol,
2),

Mid$(OldDateCol, 3, 2)
)

To prevent problems like this, never ever store Date/Time values as text,
and never store stuff like this as a number. Storing stuff like this as a
number, Access will chop off the leading zero.

Watch out for phone numbers and postal codes too. If I store a phone number
column as numeric and enter this:

215-555-1212

Access treats the hyphen as a subtraction operator, performs the arithmatic,
and stores the result (That's vile, and I've never understood why.) so the
record has -1552 in it, instead of a phone number.
--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"