MS Access - select Char as date and enter date diff

Question

I have two columns. ColA and ColB contain char (10) with data "20090520" and "20090521".

I want to select and get the difference of dates in days. I've tried using Format () and CDate () but MS Access always shows as #ERROR.

0

Clarence May 21 '09 at 14:01

4 answers

Try using DateSerial () to convert dates:

DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

+1

Gary.Ray May 21 '09 at 14:08

If at all possible, change the datatype to date datatype. You shouldn't store dates as character data.

0

HLGEM May 21 '09 at 16:40

I am connecting to another database that I have no control over. This is why this problem came up. Thanks for the feedback.

0

Krimmel May 22 '09 at 1:49 am

Andomar · Accepted Answer · 2009-05-21T14:08:09+0000

Access prefers its dates in this format:

#2009-12-01#

You can convert your date to something accessible understands with:

CDate(Format([ColA], "0000-00-00"))

Or alternatively:

DateSerial(Left([ColA],4),Mid([ColA],5,2),Right([ColA],2))

And to display the result in your preferred format:

Format(<date here>, "dd-mm-yyyy")