Problems converting Text field to Date/Time

[ChuckW]

Hi,

I have a table that has a field called InvoiceDate with a text format. The
values in the field are all in the 01152007, 04212007 type of format with the
first two digits being month, the next two being day and the last four being
year. I copied this table and created a test table out of it and attempted
to change the field from text to date time. It did this but deleted all of
the values. How can I convert these values from 01152007 to 01/15/07 without
deleting them?

Thanks,

 

[KARL DEWEY]

You need to add a new field that is a DateTime field then run an update query
converting the text to date.
Use this to convert --
DateSerial(Left([TextField],4),Right(Left([TextField],6),2),Right(Left([TextField],8),2))

 

[KARL DEWEY]

Bad post last time --
You need to add a new field that is a DateTime field then run an update query
converting the text to date.
Use this to convert --

DateSerial(Right([TextField],4),Left([TextField],2),Right(Left([TextField],4),2))
--
KARL DEWEY
Build a little - Test a little

You need to add a new field that is a DateTime field then run an update query
converting the text to date.
Use this to convert --
DateSerial(Left([TextField],4),Right(Left([TextField],6),2),Right(Left([TextField],8),2))
--
KARL DEWEY
Build a little - Test a little

Hi,

I have a table that has a field called InvoiceDate with a text format. The
values in the field are all in the 01152007, 04212007 type of format with the
first two digits being month, the next two being day and the last four being
year. I copied this table and created a test table out of it and attempted
to change the field from text to date time. It did this but deleted all of
the values. How can I convert these values from 01152007 to 01/15/07 without
deleting them?

Thanks,

 

[John W. Vinson]

Bad post last time --
You need to add a new field that is a DateTime field then run an update query
converting the text to date.
Use this to convert --

DateSerial(Right([TextField],4),Left([TextField],2),Right(Left([TextField],4),2))

or a bit more simply...

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