formatting odd date field in table

[Sabosis]

Hello-

I have a table built from an imported CSV file. The date fields comes
up as 109031, which I know represents 01/31/09 by looking at the
record in our AS400. How do I use an update query to change all of the
records in the table to show the correct date in dd/mm/yyyy format?
Someone gave me an example for excel =DATE("20"&MID(A1,2,2),1,MOD
(A1,1000)), which worked, but now I am trying to handle my report
through access. Any help would be appreciated.

Thanks-

Scott

 

[John W. Vinson]

Hello-

I have a table built from an imported CSV file. The date fields comes
up as 109031, which I know represents 01/31/09 by looking at the
record in our AS400. How do I use an update query to change all of the
records in the table to show the correct date in dd/mm/yyyy format?
Someone gave me an example for excel =DATE("20"&MID(A1,2,2),1,MOD
(A1,1000)), which worked, but now I am trying to handle my report
through access. Any help would be appreciated.

Thanks-

Scott

Try: DateSerial(1900 + [yourdate] \ 1000, 1, [yourdate] MOD 1000)

This assumes that 109 is the year (89 being 1989, 109 being 2009), and the 31
is the day of the year.

So it's very much like your function, just using the Access function
DateSerial in place of the equivalent Excel function Date().

Gotta love Microsoft's inconsistancy - in Access Date() reads the current date
from the system clock.