Changing data/time format 1

[sssu]

Hi I'm trying to change a number format to a data/time format. But it doesn't work- my entries are deleted.
Currently my numbers look like
19900231
etc
so they look like dates

Can anybody suggest anything?

Thank you

 

[KenReay]

hi

they may look like dates, but they are not dates, Access holds date time fields as a number of units elapsed since a given point in time, caanot remember the exact details, but it is not particularly relevant to your question

solution:

make a new column (myDate) of type date/time

make an update query so:

assuming your current number column is called MyNumber

and your table is called mytable

UPDATE SET MyDate = CDate(Trim(str([MyNumber]))) WHERE IsDate(Trim(str([MyNumber]));

once run this should update the new column with your dates, provided the column MyNumber holds a valid date

now in table design view, rename of delete MyNumber and rename MyDate to the previous name of MyNumber
19900231

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sssu]

Hi Ken,

Thank you for the assistance.
However, having a little difficulty.
When I use this query it says I am about to update 0 records.

Am i doing something wrong?

Thanks again

 

[KenReay]

Hi

Yes, I see having just tried it, for some reason the WHERE clause "WHERE IsDate(Trim(str([MyNumber]));" is returning false, I have tried reforming your number into a string with "/" seperators and still it does not return True

Try

UPDATE SET MyDate = CDate(Trim(str([MyNumber])));

then examine table to see if it has worked



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Sorry, it is more complicated than I thought, try:

UPDATE SET MyDate = CDate(Right(Trim(str([MyNumber])),2) & "/" & Mid(Trim(str([MyNumber])),5,2) & "/" & Left(Trim(str([MyNumber])),4)) WHERE IsDate(Right(Trim(str([MyNumber])),2) & "/" & Mid(Trim(str([MyNumber])),5,2) & "/" & Left(Trim(str([MyNumber])),4)) ;


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sssu]

Your wonderful!
Thank you