Changing a text field to a date field

[A_Classic_Man]

I have a table that has dates that have been entered as follows:
10-28-07. The field is formatted as a text field. I need to convert
the dates to date format 10/28/07. I have tried using DateSerial in an
update query but am having trouble getting it to work.

What is the proper way to do this?

Thanks

 

[akphidelt]

Alright, not to sure if this works... but you can try this

Go to the table which has the dates, highlight the column. Right click on
the column and go to find. Then click on the replace tab.

In the find what type -
In the replace with type /

Make sure you change the Match to "Any Part of Field"

Then click replace all... then go back in to the design portion and change
to date. I know this works, the only part im not sure of is if it changes the
existing data to date values... but its worth a shot.

 

[Jeff Boyce]

Are you saying that you want to convert the text to date, or that you merely
wish to change its appearance (i.e., "format")?

If the field in the table is text, it isn't Date/Time. If you want to have
a Date/Time value, you need a (?new) Date/Time field.

You could convert that text string into a Date/Time value with DateSerial
and Left(), Mid() and Right() functions in a query and update the Date/Time
field.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[A_Classic_Man]

Alright, not to sure if this works... but you can try this

Go to the table which has the dates, highlight the column. Right click on
the column and go to find. Then click on the replace tab.

In the find what type -
In the replace with type /

Make sure you change the Match to "Any Part of Field"

Then click replace all... then go back in to the design portion and change
to date. I know this works, the only part im not sure of is if it changes the
existing data to date values... but its worth a shot.







- Show quoted text -

This is not just a one time thing. The dates are placed in the table
from a Word document throught VB code. The date is used to name the
Word document so slashes cannot be used. If possible, I would like to
change the field format through a query that can be run by a macro.

Thanks for the suggestion

 

[A_Classic_Man]

I tried this out, just for kicks, and it works...kind of! It only replaced
the some of the dashes the first time round, but if you click Replace All a
second time, it completes the job! Also, when converting the field to
Date/Time, it'll change the year to YYYY instead of the YY format you have.. I
think you could select the MM/DD/YY format when converting to Date/Time only
**IF** you're running a version of Windows (like 98 SE) that supports this
Short Date Format.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted viahttp://www.accessmonster.com

Thanks for the responses
Jeff, your suggestion is what I'm looking for. The field the text
formated data is in is called EventDate with an input mask
99\-99\-00;0;_

I added another field (EventDateCompleted) to my table and set the
field to Date/Time and Short Date format. Imput mask is 99/99/00;0;_

I have an update query with the following expression:
DateSerial(Left([EventDate],2),Mid([EventDate],2),Right([EventDate],
2)).

When I run the query, I get the following: Type conversion error.
What am I missing?

Thanks

 

[Jeff Boyce]

10-28-07

Different date structures in different locales ... is that October 28th,
2007, or the 10th of Month=28 (nonsense, I know!), 2007?

Your use of DateSerial may be slightly off. Take a look at the syntax for
DateSerial() ... I believe it starts with the YY value...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks for the responses
Jeff, your suggestion is what I'm looking for. The field the text
formated data is in is called EventDate with an input mask
99\-99\-00;0;_

I added another field (EventDateCompleted) to my table and set the
field to Date/Time and Short Date format. Imput mask is 99/99/00;0;_

I have an update query with the following expression:
DateSerial(Left([EventDate],2),Mid([EventDate],2),Right([EventDate],
2)).

When I run the query, I get the following: Type conversion error.
What am I missing?

Thanks

 

[A_Classic_Man]

10-28-07

Different date structures in different locales ... is that October 28th,
2007, or the 10th of Month=28 (nonsense, I know!), 2007?

Your use of DateSerial may be slightly off.  Take a look at the syntax for
DateSerial() ... I believe it starts with the YY value...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the responses
Jeff, your suggestion is what I'm looking for. The field the text
formated data is in is called EventDate with an input mask
99\-99\-00;0;_

I added another field (EventDateCompleted) to my table and set the
field to Date/Time and Short Date format. Imput mask is 99/99/00;0;_

I have an update query with the following expression:
DateSerial(Left([EventDate],2),Mid([EventDate],2),Right([EventDate],
2)).

When I run the query, I get the following: Type conversion error.
What am I missing?

Thanks

Jeff

Thanks for your time. CDate works

 

[David W. Fenton]

I
think you could select the MM/DD/YY format when converting to
Date/Time only **IF** you're running a version of Windows (like 98
SE) that supports this Short Date Format.

Since Win95, the number of digits displayed in the year is
determined by the regional settings in Control Panel. If you set it
to 2, it will display 2, if you set it to 4, then 4.

It is exactly the same in WinXP (and I'd assume the same for Vista),
though the default is now 4 digits instead of 2.

None of these should make the slightest difference for Access, which
uses the 2029 window for interpreting the date, except if you have
dates in the period 1900-1929.