DateDiff will not return anything?

[anoble1]

Hi guys,
I am trying to return the number of days from a start date and an End Date. my data source has the format yyyymmdd or 20190101. I get a #Error when I run the query.

Expr1: DateDiff("d",[QS36F_PARTHIST]![PH_DTR],[QS36F_PURORD]![PO_DTR])

Is it because my source is in that format and won't work?

[jimatqsi]

Yes, you'll have to reformat the date fields to mm/dd/yy or whatever your local format is.

Jim

[cactusdata]

The format of a date value is for display only. So, most likely, your dates are not DateTime but Text. Thus, first convert these to DateTime, then use DateDiff:

Expr1: DateDiff("d",CDate(Format([QS36F_PARTHIST]![PH_DTR],"@@@@\/@@\/@@")),CDate(Format([QS36F_PURORD]![PO_DTR],"@@@@\/@@\/@@")))

[ADezii]

You could also use the DateSerial() Function which will return a Variant (Date):

Dim strStart As String
Dim strEnd As String
 
strStart = "20190101"
strEnd = "20200101"
 
'Will OUTPUT 365
Debug.Print DateDiff("d", DateSerial(Left(strStart, 4), Mid(strStart, 5, 2), Right(strStart, 2)), _
                          DateSerial(Left(strEnd, 4), Mid(strEnd, 5, 2), Right(strEnd, 2)))

[twinnyfo]

Also, simply converting the text to the following will work:

strDate = Left([PH_DTR], 4) & "-" & _
          Mid([PH_DTR], 5, 2) & "-" & _
          Right([PH_DTR], 2)

This produces a string with the format: "2019-01-01", which is a universally recognized Date format in Access.

As mentioned by others, it is absolutely required that you transmogrify your text value into some form of recognizable "date".

Hope this hepps.

[NeoPa]

"Is it because my source is in that format and won't work?"
Yes. Definitely.

Access does a very powerful & flexible job of converting data for you automatically between various different types. The string data you're using is not in a form that can be recognised as a date. There are many forms that Access will recognise, but that is certainly not one of them.

My advice would mirror CactusData's and be to convert your string values into actual date values in order to pass to the DateDiff() function but the alternatives, of converting it to different string values that ARE recognisable as dates, will also work perfectly well for you.

What they don't do so well is leave you with the understanding of what's going on such that you will never need to ask such a question again. That's one of the drawbacks with coding in VBA. It's very flexible and thus allows people to work with it even when they have limited understanding of why it does what it does. The more you get into it though, the more important that understanding becomes.

Take that understanding away from this question and you will surely have benefitted.