472,102 Members | 1,361 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,102 software developers and data experts.

DateDiff will not return anything?

244 128KB
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.
Expand|Select|Wrap|Line Numbers
  1. Expr1: DateDiff("d",[QS36F_PARTHIST]![PH_DTR],[QS36F_PURORD]![PO_DTR])
Is it because my source is in that format and won't work?
Sep 23 '20 #1
5 2397
1,263 Expert 1GB
Yes, you'll have to reformat the date fields to mm/dd/yy or whatever your local format is.

Sep 23 '20 #2
202 Expert 128KB
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:

Expand|Select|Wrap|Line Numbers
  1. Expr1: DateDiff("d",CDate(Format([QS36F_PARTHIST]![PH_DTR],"@@@@\/@@\/@@")),CDate(Format([QS36F_PURORD]![PO_DTR],"@@@@\/@@\/@@")))
Sep 23 '20 #3
8,830 Expert 8TB
You could also use the DateSerial() Function which will return a Variant (Date):
Expand|Select|Wrap|Line Numbers
  1. Dim strStart As String
  2. Dim strEnd As String
  4. strStart = "20190101"
  5. strEnd = "20200101"
  7. 'Will OUTPUT 365
  8. Debug.Print DateDiff("d", DateSerial(Left(strStart, 4), Mid(strStart, 5, 2), Right(strStart, 2)), _
  9.                           DateSerial(Left(strEnd, 4), Mid(strEnd, 5, 2), Right(strEnd, 2)))
Sep 23 '20 #4
3,653 Expert Mod 2GB
Also, simply converting the text to the following will work:

Expand|Select|Wrap|Line Numbers
  1. strDate = Left([PH_DTR], 4) & "-" & _
  2.           Mid([PH_DTR], 5, 2) & "-" & _
  3.           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.
Sep 24 '20 #5
32,496 Expert Mod 16PB
"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.
Dec 22 '20 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Doug | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.