Convert Text to Date Error

A

Andi Lee Davis

Please help. The Text I have stores the PO number based on a format date() as
"ddmmyy" a six digit format, which is stored in a text field. I need to now
input a date and have created a new field. I was going to update the PO's but
converting the date from the text and putting that into the new field. You
can see the conversion problem I have updating the TEXT field back to a DATE,
even if I use CDATE the date is calculated on the number rather than using
the actual figures.

TEXT ActualData CalculatedDate
010605 10605 12/01/1929
010705 10705 22/04/1929
010805 10805 31/07/1929
020605 20605 30/05/1956
020805 20805 16/12/1956
TEXT ActualData CalculatedDate
150805 150805 20/11/2312
160505 160505 12/06/2339
160605 160605 20/09/2339

As you can see Access is working out the dates by the actual figure when it
formats it.

How can I solve this problem.

Any help much appreciates
 
D

Dennis

[CalculatedDate] = CDate(Left([Text],2) & "/" & Mid([Text],3,2) & "/" &
Right([Text],2))
 
A

Andi Lee Davis

Thanks V. Much

That's exactly what I needed to do to update all the blank fields.

Andi

Dennis said:
[CalculatedDate] = CDate(Left([Text],2) & "/" & Mid([Text],3,2) & "/" &
Right([Text],2))

Andi Lee Davis said:
Please help. The Text I have stores the PO number based on a format date() as
"ddmmyy" a six digit format, which is stored in a text field. I need to now
input a date and have created a new field. I was going to update the PO's but
converting the date from the text and putting that into the new field. You
can see the conversion problem I have updating the TEXT field back to a DATE,
even if I use CDATE the date is calculated on the number rather than using
the actual figures.

TEXT ActualData CalculatedDate
010605 10605 12/01/1929
010705 10705 22/04/1929
010805 10805 31/07/1929
020605 20605 30/05/1956
020805 20805 16/12/1956
TEXT ActualData CalculatedDate
150805 150805 20/11/2312
160505 160505 12/06/2339
160605 160605 20/09/2339

As you can see Access is working out the dates by the actual figure when it
formats it.

How can I solve this problem.

Any help much appreciates
Click to expand...
Click to expand...
 
Top