format dates from text

  • Thread starter Thread starter Paul G
  • Start date Start date
P

Paul G

I have a column that is text formatted but is actually
showing dates in yyyymmdd format. When I open the table
and try to convert the text data to date format in
yyyymmdd Access attempts to delete all my date.

I want to format the text as date and ultimately change it
to yymmdd format.

Any ideas?

Thanks

Paul
 
How are you trying to do the conversion? By changing the field type with the
table in Design Mode?

Add a new field of Date/Time to the table. Write an Update query to convert
the text date to a proper date, which will be stored in the new Date/Time
field. You can use the DateSerial function like:

IIf(IsNull([TextDate]), Null, DateSerial(Left([TextDate], 4),
Mid([TextDate], 5, 2), Right([TextDate], 2)))

Once you've done this, then you can delete the old text date field.
 
I am not really sure how to put this code into access.
Also should "textdate" be the name of my new field????

P
-----Original Message-----
How are you trying to do the conversion? By changing the field type with the
table in Design Mode?

Add a new field of Date/Time to the table. Write an Update query to convert
the text date to a proper date, which will be stored in the new Date/Time
field. You can use the DateSerial function like:

IIf(IsNull([TextDate]), Null, DateSerial(Left([TextDate], 4),
Mid([TextDate], 5, 2), Right([TextDate], 2)))

Once you've done this, then you can delete the old text date field.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I have a column that is text formatted but is actually
showing dates in yyyymmdd format. When I open the table
and try to convert the text data to date format in
yyyymmdd Access attempts to delete all my date.

I want to format the text as date and ultimately change it
to yymmdd format.

Any ideas?

Thanks

Paul
Click to expand...


.
Click to expand...
 
I was using TextDate as the name of the field with the date in text format.
Replace that with whatever your field is named.

Once you've created the new Date field, all you need is an Update query, no
code per se.

Create a query, add the table and drag both the old and new Date fields to
the grid. Change the query to an Update query (you can do this from the
Query menu when the query's open in Design mode). That will add a row
labelled "Update To" to the grid. Paste

DateSerial(Left([TextDate], 4), Mid([TextDate], 5, 2), Right([TextDate],
2))

into the cell on the Update To row that's under the new Date field, and

Is Not Null

into the cell on the Criteria cell under the old date field.

Click on the Exclamation mark to run the query, and you're done. You don't
even need to save the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Paulg said:
I am not really sure how to put this code into access.
Also should "textdate" be the name of my new field????

P
-----Original Message-----
How are you trying to do the conversion? By changing the field type with the
table in Design Mode?

Add a new field of Date/Time to the table. Write an Update query to convert
the text date to a proper date, which will be stored in the new Date/Time
field. You can use the DateSerial function like:

IIf(IsNull([TextDate]), Null, DateSerial(Left([TextDate], 4),
Mid([TextDate], 5, 2), Right([TextDate], 2)))

Once you've done this, then you can delete the old text date field.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I have a column that is text formatted but is actually
showing dates in yyyymmdd format. When I open the table
and try to convert the text data to date format in
yyyymmdd Access attempts to delete all my date.

I want to format the text as date and ultimately change it
to yymmdd format.

Any ideas?

Thanks

Paul
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top