change a field into date format

[basara]

Hi,
In my table, I have a field of text format. It is something like this [01 01
2005]. I need to change it to date format either jan/01/2005 or Jan/2005.
Is there a way of doing this, by writting an update query or VBA code?
Thanks very much.

 

[Allen Browne]

The safest way would be:
1. Add a new date/time field to the table.
2. Populate it with an Update query.
3. After verifying all the data is corect, remove the text date.
4. Set the Format property of the new date field so the date is displayed as
you wish.

It is possible to programmatically change the field type, but I would not
recommend it for a date/time field. There is too much potential for the
dates to be misinterpreted, and there's no second chance. If you want to do
it anyway, it would be something like this:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField DATE;"
DBEngine(0)(0).Execute strSql, dbFailOnError

 

[basara]

Hi,

I need help with the update query, since the original text date is in this
form " 01 01 2005" I need to assign the day, month and year of the new date
field to be the respective number. something like [column].[Day]=
Instr([date], first 2 number), i am not sure about the equation. how can i
do it?
Thanks a lot.

--
someone in trouble

The safest way would be:
1. Add a new date/time field to the table.
2. Populate it with an Update query.
3. After verifying all the data is corect, remove the text date.
4. Set the Format property of the new date field so the date is displayed as
you wish.

It is possible to programmatically change the field type, but I would not
recommend it for a date/time field. There is too much potential for the
dates to be misinterpreted, and there's no second chance. If you want to do
it anyway, it would be something like this:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField DATE;"
DBEngine(0)(0).Execute strSql, dbFailOnError

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

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

Hi,
In my table, I have a field of text format. It is something like this [01
01
2005]. I need to change it to date format either jan/01/2005 or Jan/2005.
Is there a way of doing this, by writting an update query or VBA code?
Thanks very much.

 

[Steve Schapel]

Basara,

This should do it...
CDate([YourTextField])

 

[Allen Browne]

Try something like this:
CDate(Replace("01 01 2005", " ", "/"))

That should work fine, though Access 2000 did have problems with Replace()
in a query.

Another alternative would be to parse the parts of the date with Left(),
Mid(), Right(), Instr(), and put them into DateSerial().

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

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

I need help with the update query, since the original text date is in this
form " 01 01 2005" I need to assign the day, month and year of the new
date
field to be the respective number. something like [column].[Day]=
Instr([date], first 2 number), i am not sure about the equation. how can
i
do it?
Thanks a lot.

--
someone in trouble

The safest way would be:
1. Add a new date/time field to the table.
2. Populate it with an Update query.
3. After verifying all the data is corect, remove the text date.
4. Set the Format property of the new date field so the date is displayed
as
you wish.

It is possible to programmatically change the field type, but I would not
recommend it for a date/time field. There is too much potential for the
dates to be misinterpreted, and there's no second chance. If you want to
do
it anyway, it would be something like this:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField DATE;"
DBEngine(0)(0).Execute strSql, dbFailOnError

Hi,
In my table, I have a field of text format. It is something like this
[01
01
2005]. I need to change it to date format either jan/01/2005 or
Jan/2005.
Is there a way of doing this, by writting an update query or VBA code?

 

[basara]

Thanks, Allen and Steve. This is so efficient.
Now my problem solved. Love Access
--
someone in trouble

Try something like this:
CDate(Replace("01 01 2005", " ", "/"))

That should work fine, though Access 2000 did have problems with Replace()
in a query.

Another alternative would be to parse the parts of the date with Left(),
Mid(), Right(), Instr(), and put them into DateSerial().

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

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

I need help with the update query, since the original text date is in this
form " 01 01 2005" I need to assign the day, month and year of the new
date
field to be the respective number. something like [column].[Day]=
Instr([date], first 2 number), i am not sure about the equation. how can
i
do it?
Thanks a lot.

--
someone in trouble

The safest way would be:
1. Add a new date/time field to the table.
2. Populate it with an Update query.
3. After verifying all the data is corect, remove the text date.
4. Set the Format property of the new date field so the date is displayed
as
you wish.

It is possible to programmatically change the field type, but I would not
recommend it for a date/time field. There is too much potential for the
dates to be misinterpreted, and there's no second chance. If you want to
do
it anyway, it would be something like this:
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField DATE;"
DBEngine(0)(0).Execute strSql, dbFailOnError

Hi,
In my table, I have a field of text format. It is something like this
[01
01
2005]. I need to change it to date format either jan/01/2005 or
Jan/2005.
Is there a way of doing this, by writting an update query or VBA code?