ddmmccyy to dd/mm/yyyy format

G

Guest

Hi,

from an ODBC driver I am taking data from a system that includes a date
field (in number format). This comes direct from the system in ddmmccyy
format and I am using:

Audit Date: (Left([ICDATE],2)) & "/" & (Right(Left([ICDATE],4),2)) & "/" &
(Right([ICDATE],4)) where ICDATE is the data field name.

To get the format right. I need to use a function to convert the field in
Access from Number to Date/Time.

Secondly I need to deal with the issue for days <10 as this comes across as
1062006 and hence throws the above formula. If all of this can be in one
formula, all to the good.

Thanks
 
A

Allen Browne

Try:
DateSerial(Right([ICDate], 4),
Mid(Format([ICDate], "00000000"), 3, 2),
Left(Format([ICDate], "00000000"), 2))

Assumes the ICDATE field can be recognised as a Number.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ccyymmdd to dd/mm/yyyy from query 2
Format Date "dd/mm/yyyy" 1
Forcing a date entry format of dd/mm/yyyy 1
create input mask "yyyy-mm-dd" 5
convert date format from mm/dd/yyyy to dd/mm/yyyy 3
dd/MM/YYYY in SQL & Ctrl Panel but access read it as MM/dd/YYYY 2
Format DATETIME to Date in query on linked table 4
How to enter a date in DD-MM-YYYY format 2

Top