By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,153 Members | 662 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,153 IT Pros & Developers. It's quick & easy.

DateDiff will not return anything?

anoble1
100+
P: 231
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
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,244
Yes, you'll have to reformat the date fields to mm/dd/yy or whatever your local format is.

Jim
Sep 23 '20 #2

cactusdata
Expert 100+
P: 122
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

ADezii
Expert 5K+
P: 8,748
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
  3.  
  4. strStart = "20190101"
  5. strEnd = "20200101"
  6.  
  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

twinnyfo
Expert Mod 2.5K+
P: 3,542
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.
4 Weeks Ago #5

Post your reply

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