text to date conversion

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in a table that I need to convert from text to date (123,000
records). Doing this in the design view results in an error message :not
enought disk space or memory. I know I have enough of either. I have also
cleared the clipboard, if this is part of the conversion, but no luck. What
kind of query can I use to accomplish this and what would be the expression?
 
I would add a new field and then use an update query to update the new field
with the date value. Then remove the previous field and rename the new
field.
 
Instead of trying to convert the field, add a new one, populate it, and
after verifying everything is okay, delete the old one.

Compact the database:
Tools | Database Utilities | Compact

Add a new date/time type field to your table.

Create a query using this table.

Change it to an Update query (Update on Query menu.)

In the Criteria row under the text-date field, enter:
Is Not Null

In the Update row, enter the expression that converts it to a date. For
example, if the text is a number in the format:
yyyymmdd
you would use:
DateSerial(Left([d],4), Mid([d],5,2), Right([d],2))

After executing the query, and verifying the results are correct, you can
delete the old text field, and rename the new one if you wish. Compact
again.
 
Thanks, I understand. Please give me the expression if my date format is
01/01/2006

Allen Browne said:
Instead of trying to convert the field, add a new one, populate it, and
after verifying everything is okay, delete the old one.

Compact the database:
Tools | Database Utilities | Compact

Add a new date/time type field to your table.

Create a query using this table.

Change it to an Update query (Update on Query menu.)

In the Criteria row under the text-date field, enter:
Is Not Null

In the Update row, enter the expression that converts it to a date. For
example, if the text is a number in the format:
yyyymmdd
you would use:
DateSerial(Left([d],4), Mid([d],5,2), Right([d],2))

After executing the query, and verifying the results are correct, you can
delete the old text field, and rename the new one if you wish. Compact
again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

George Walters said:
I have a field in a table that I need to convert from text to date (123,000
records). Doing this in the design view results in an error message :not
enought disk space or memory. I know I have enough of either. I have
also
cleared the clipboard, if this is part of the conversion, but no luck.
What
kind of query can I use to accomplish this and what would be the
expression?
Click to expand...
Click to expand...
 
Thanks, I understand. Please give me the correct expression if my present
text format 01/01/2000. Thanks

Allen Browne said:
Instead of trying to convert the field, add a new one, populate it, and
after verifying everything is okay, delete the old one.

Compact the database:
Tools | Database Utilities | Compact

Add a new date/time type field to your table.

Create a query using this table.

Change it to an Update query (Update on Query menu.)

In the Criteria row under the text-date field, enter:
Is Not Null

In the Update row, enter the expression that converts it to a date. For
example, if the text is a number in the format:
yyyymmdd
you would use:
DateSerial(Left([d],4), Mid([d],5,2), Right([d],2))

After executing the query, and verifying the results are correct, you can
delete the old text field, and rename the new one if you wish. Compact
again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

George Walters said:
I have a field in a table that I need to convert from text to date (123,000
records). Doing this in the design view results in an error message :not
enought disk space or memory. I know I have enough of either. I have
also
cleared the clipboard, if this is part of the conversion, but no luck.
What
kind of query can I use to accomplish this and what would be the
expression?
Click to expand...
Click to expand...
 
George, follow the example, and you can use Left(), Mid() and Right() to
parse out the relevant parts of the date, and DateSerial() to build the
result.

To check you are getting it right open the Immediate Window (Ctrl+G) and
practice there. For example, if the 01 i the middle is day (it could be
month if you are in the UK), in the Immediate window you would enter:
? Mid("01/01/01", 4,2)
and see that you get the right part.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

George Walters said:
Thanks, I understand. Please give me the correct expression if my present
text format 01/01/2000. Thanks

Allen Browne said:
Instead of trying to convert the field, add a new one, populate it, and
after verifying everything is okay, delete the old one.

Compact the database:
Tools | Database Utilities | Compact

Add a new date/time type field to your table.

Create a query using this table.

Change it to an Update query (Update on Query menu.)

In the Criteria row under the text-date field, enter:
Is Not Null

In the Update row, enter the expression that converts it to a date. For
example, if the text is a number in the format:
yyyymmdd
you would use:
DateSerial(Left([d],4), Mid([d],5,2), Right([d],2))

After executing the query, and verifying the results are correct, you can
delete the old text field, and rename the new one if you wish. Compact
again.

message
I have a field in a table that I need to convert from text to date
(123,000
records). Doing this in the design view results in an error message
:not
enought disk space or memory. I know I have enough of either. I have
also
cleared the clipboard, if this is part of the conversion, but no luck.
What
kind of query can I use to accomplish this and what would be the
expression?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top