DateDiff Access Query returning #Error

Question

I am trying to get the number of days between two dates. Here is the Expression I am using in Access Query Designer. (Access 2007)

Expr1: DateDiff('d',[Accept Date],[Sent Date])

The query is returning #Error.

The [Accept Date] and [Sent Date] format is yyyymmdd and the Data Type is TEXT. I changed it to Date/Time but Access purged the data.

Here is the SQL behind the Query if that helps:

SELECT RTG.[PRO NO], RTG.[Service], RTG.[Sent Date], RTG.[Accept Date], DateDiff('d',[Accept Date],[Sent Date]) AS Expr1
FROM RTG
WHERE (((RTG.[PRO NO Prefix])<>"215") AND ((RTG.[Invoice Number])="8548"));

This has got to be something simple, right?

HansUp HansUp · Accepted Answer · 2014-10-09T14:41:21

DatePart can accept dates which are actually text instead of Date/Time datatype. However, the text must be something which Access recognizes as a valid date representation. A text date in "yyyymmdd" format doesn't satisfy that requirement. For example, in the Immediate window ...

? IsDate("20141009")
False

However, if you insert dashes between the year, month, and day segments, Access can recognize the text string as a date.

? Format("20141009", "0000-00-00")
2014-10-09
? IsDate(Format("20141009", "0000-00-00"))
True

Test that technique in a simple query to make sure it avoids the error.

SELECT
    RTG.[Sent Date],
    RTG.[Accept Date],
    DateDiff(
        'd',
        Format([Accept Date],'0000-00-00'),
        Format([Sent Date],'0000-00-00')
    ) AS Expr1
FROM RTG

If Access still throws an error, use CDate to cast the text date to Date/Time datatype.

    DateDiff(
        'd',
        CDate(Format([Accept Date],'0000-00-00')),
        CDate(Format([Sent Date],'0000-00-00'))
    ) AS Expr1

Your task would be less challenging with actual Date/Time fields. You could create new Date/Time fields and execute an UPDATE query to load the transformed values from the old text date fields.

DateDiff Access Query returning #Error

1 Answers