Question Want to convert Text format into date in MS Access Database

[pwint]

I want to convert the text field into date format.
But the problem is data are already there as a format.
39267
When I tried to convert in design view, the message shows that xxx records will be deleted.
But i don't want to delete.
Please help me.
Thanks in advance.

 

[jdraw]

I want to convert the text field into date format.
But the problem is data are already there as a format.
39267
When I tried to convert in design view, the message shows that xxx records will be deleted.
But i don't want to delete.
Please help me.
Thanks in advance.

What text field? Please help us help you.
What goes in and what do you expect out? Sample please.

 

[Galaxiom]

The ones to be deleted probably include alpha characters.

Find them with a query selectiing False from a derived field
GoodDates: IsNumeric(textdatefield)

 

[pwint]

I tried to run the query but it doesn't show anything.
What is the alpha character ?
Can I remove manually?
Thanks for your advice.

 

[Galaxiom]

What is the alpha character ?

A letter of the ALPHAbet.
Basically that query looked for anything that wasn't a number.

Maybe it isn't the problem.

 

[Galaxiom]

Some of the values might be out of range.
For conversion to a date the original number must be between -657434 and 2958465

These figures represent 01/01/100 CE and 31/12/9999 CE

 

[Guus2005]

The number 39267 is the long type representation of the date field.
You can convert that field using an extra field and an update statement.

Create another field in your table. Call it ConvDate type: Date.
Create a query:

update yourTablename set ConvDate = cdate(yourLongDate) where isnumeric(yourLongDate) = true

After that, you can replace the original field with the new "ConvDate" field.

Enjoy!

 

[Galaxiom]

The number 39267 is the long type representation of the date field.
You can convert that field using an extra field and an update statement.

I expect this will run into the same problem. A2007 (at least) is smart enough to do the conversion automatically from text or number type fields by simply redefining the field type to Date.

update yourTablename set ConvDate = cdate(yourLongDate) where isnumeric(yourLongDate) = true

BTW When a statement has a boolean result the = True can be omitted.

Where IsNumeric(yourLongDate) will do the job.