Converting number into a date?

[sandingdude]

I have to pull data from the AS400 into a MS Access Database. Our dates in the AS400 are yy/mm/dd and get pulled in as a number data type. I'm having problems coverting the number data type into a date data type. I need to convert into month/date/year. Any suggestions on format, input mask, ideas, anything you guys can think of would be great. Thanks.

 

[KenReay]

Hi

First I assume you mean YYMMDD since yy/mm/dd is not an allowable value for a number field

MyDate = CDate(right(str(nDate),2) & "/" & Mid(str(nDate),3,2) & "/" & Right(str(nDate),2))

You may need to shuffle the sequence of the MM and DD bit, I am not sure if the locale affects this, I am in Uk whwre dd/mm/yy id the 'normal' locale setting for date

Regards

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

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

UK

 

[sandingdude]

Thanks Ken,

Would I put this code in the format area after I change it to a date/time data type? (In the table design view)

Thanks. Dave

 

[KenReay]

Hi

No you would have to do it in code after you have imported the data

Regards

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

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

UK

 

[sandingdude]

Ken

Can you give me some directions on how to do it? I've never did any coding in Access before. Thanks.