CODEWITHSUNDEEP
sqlms-access
Clarence
Clarence

Reputation:

MS Access - Select Char as Date and doing a date diff

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

I want to select and get the date difference in days. I have tried using Format() and CDate() but MS Access always display as #ERROR.

Upvotes: 0

Views: 2307

Answers (4)

Krimmel
Krimmel

Reputation: 11

I am connecting to another database which I have no control on. That is why this problem occurred. Thanks for the feedback.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96600

If at all possible, change the datatype to a date datatype. You should not store dates as character data.

Upvotes: 0

Andomar
Andomar

Reputation: 238176

Access prefers its dates in this format:

#2009-12-01#

You can convert your date to something Access 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")

Upvotes: 3

Gary.Ray
Gary.Ray

Reputation: 6501

Try using DateSerial() to convert the dates:

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

Upvotes: 1

Related Questions