Error with DateDiff() (1 Viewer)

12asd

Registered User.
Local time
Today, 09:50
Joined
Dec 11, 2017
Messages
15
This goes with both tables and queries but I'm trying to get the difference between two dates and times in a table.

The code I'm using for the default value for a field in a table is:
Code:
=DateDiff("m/d h:n:s",[Date/time reported],[Date/time resolved],"vbMonday")

Where [Date/time reported] and [Date/time resolved] are fields in the same table in the form dd/mm/yyyy hh/nn/ss.

I get the error: The database engine does not recognize either the field 'Date/time reported' in a validation expression, or the default value in the table 'TBL Request'. when I try to run the table.

I want to find the time passed between the two dates in the form of months, days, hours, minutes and seconds.

I am using the code for a field to see what it outputs so it can be transferred to a query.

I haven't used DateDiff() before and am unsure on how to exactly use it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:50
Joined
May 7, 2009
Messages
19,169
First there should be ni quote ob vbMonday.
Second datediff accepts only m, d, s, n, yyyy sa first parameter.
 

12asd

Registered User.
Local time
Today, 09:50
Joined
Dec 11, 2017
Messages
15
First there should be ni quote ob vbMonday.
Second datediff accepts only m, d, s, n, yyyy sa first parameter.

The quote was put in automatically. What set of parameters would get me month, day, minutes and seconds?
 

isladogs

MVP / VIP
Local time
Today, 09:50
Joined
Jan 14, 2017
Messages
18,186
You could either do 4 separate DateDiff items ...
or just use 's' to get the value in seconds then do a further calculation to convert that to the format you want but that will have issues with number of days in each month

Are you sure you don't want hours as well?

However if there are say 25 days between events, do you really need to know hours, mins and seconds?
 

12asd

Registered User.
Local time
Today, 09:50
Joined
Dec 11, 2017
Messages
15
You could either do 4 separate DateDiff items ...
or just use 's' to get the value in seconds then do a further calculation to convert that to the format you want but that will have issues with number of days in each month

Are you sure you don't want hours as well?

However if there are say 25 days between events, do you really need to know hours, mins and seconds?

Yes, but as arnelgp said, hours weren't supported apprently.

I would guess so.

Though what about the error I got? What does it mean and how do I fix it?
 

Minty

AWF VIP
Local time
Today, 09:50
Joined
Jul 26, 2013
Messages
10,353
The error is the criteria at the beginning of the statement, and the quotes around vbMonday - so to get the hours for instance it should be;
Code:
=DateDiff("h",[Date/time reported],[Date/time resolved],vbMonday)

You can only use one type of calculation return at a time, not multiple ones.
If you did it in minutes;
Code:
=DateDiff("n",[Date/time reported],[Date/time resolved],vbMonday)
Then you can easily use some maths to get a hours and minutes displayed.

Oh and please change those field names - spaces and special characters should be avoided at all costs as it makes you have to use square brackets all the time and makes things difficult to read. ReportedDate and ResolvedDate would be much nicer. IMHO ;)
 

12asd

Registered User.
Local time
Today, 09:50
Joined
Dec 11, 2017
Messages
15
The error is the criteria at the beginning of the statement, and the quotes around vbMonday - so to get the hours for instance it should be;
Code:
=DateDiff("h",[Date/time reported],[Date/time resolved],vbMonday)

You can only use one type of calculation return at a time, not multiple ones.
If you did it in minutes;
Code:
=DateDiff("n",[Date/time reported],[Date/time resolved],vbMonday)
Then you can easily use some maths to get a hours and minutes displayed.

Oh and please change those field names - spaces and special characters should be avoided at all costs as it makes you have to use square brackets all the time and makes things difficult to read. ReportedDate and ResolvedDate would be much nicer. IMHO ;)

The field names were already there, this is a practice table I'm working on and I'd rather keep the default names.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:50
Joined
May 7, 2009
Messages
19,169
try this function i modified.
copy and paste on standard module.
can be called in query or as expression.
on your case:

=Diff3Dates("m/d hh:nn:ss", [Date/Time reported], [Date/Time resolved])

i think there is no bearing if you put vbMonday there, since
we are interested on the difference.

Code:
Public Function Diff3Dates(Interval As String, Date1 As Variant, Date2 As Variant, _
Optional ShowZero As Boolean = True) As Variant
'Author:    ? Copyright 2001 Pacific Database Pty Limited
'           Graham R Seach MCP MVP gseach@pacificdb.com.au
'           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
'           This code is freeware. Enjoy...
'           (*) Amendments suggested by Douglas J. Steele MVP
'
'Description:   This function calculates the number of years,
'               months, days, hours, minutes and seconds between
'               two dates, as elapsed time.
'
'Inputs:    Interval:   Intervals to be displayed (a string)
'           Date1:      The lower date (see below)
'           Date2:      The higher date (see below)
'           ShowZero:   Boolean to select showing zero elements
'
'Outputs:   On error: Null
'           On no error: Variant containing the number of years,
'               months, days, hours, minutes & seconds between
'               the two dates, depending on the display interval
'               selected.
'           If Date1 is greater than Date2, the result will
'               be a negative value.
'           The function compensates for the lack of any intervals
'               not listed. For example, if Interval lists "m", but
'               not "y", the function adds the value of the year
'               component to the month component.
'           If ShowZero is True, and an output element is zero, it
'               is displayed. However, if ShowZero is False or
'               omitted, no zero-value elements are displayed.
'               For example, with ShowZero = False, Interval = "ym",
'               elements = 0 & 1 respectively, the output string
'               will be "1 month" - not "0 years 1 month".
'
'Modified by:   ArnelGP
'
'take into account the format that the user wants.
'

On Error GoTo Err_Diff2Dates

   Dim booCalcYears As Boolean
   Dim booCalcMonths As Boolean
   Dim booCalcDays As Boolean
   Dim booCalcHours As Boolean
   Dim booCalcMinutes As Boolean
   Dim booCalcSeconds As Boolean
   Dim booCalcWeeks As Boolean
   Dim booSwapped As Boolean
   Dim dtTemp As Date
   Dim intCounter As Integer
   Dim lngDiffYears As Long
   Dim lngDiffMonths As Long
   Dim lngDiffDays As Long
   Dim lngDiffHours As Long
   Dim lngDiffMinutes As Long
   Dim lngDiffSeconds As Long
   Dim lngDiffWeeks As Long
   Dim varTemp As Variant

   Dim sFormat As String
   
   Const INTERVALS As String = "dmyhnsw"

'Check that Interval contains only valid characters
   Interval = LCase$(Interval)
   For intCounter = 1 To Len(Interval)
      If InStr("/: ", Mid(Interval, intCounter, 1)) > 0 Then
        'ignore
      Else
        If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
           Exit Function
        End If
     End If
   Next intCounter

'Check that valid dates have been entered
   If IsNull(Date1) Then Exit Function
   If IsNull(Date2) Then Exit Function
   If Not (IsDate(Date1)) Then Exit Function
   If Not (IsDate(Date2)) Then Exit Function

'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
   If Date1 > Date2 Then
      dtTemp = Date1
      Date1 = Date2
      Date2 = dtTemp
      booSwapped = True
   End If

   Diff3Dates = Null
   varTemp = Null

'What intervals are supplied
   booCalcYears = (InStr(1, Interval, "y") > 0)
   booCalcMonths = (InStr(1, Interval, "m") > 0)
   booCalcDays = (InStr(1, Interval, "d") > 0)
   booCalcHours = (InStr(1, Interval, "h") > 0)
   booCalcMinutes = (InStr(1, Interval, "n") > 0)
   booCalcSeconds = (InStr(1, Interval, "s") > 0)
   booCalcWeeks = (InStr(1, Interval, "w") > 0)

'Get the cumulative differences
   If booCalcYears Then
      lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
              IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
      Date1 = DateAdd("yyyy", lngDiffYears, Date1)
   End If

   If booCalcMonths Then
      lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
              IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
      Date1 = DateAdd("m", lngDiffMonths, Date1)
   End If

   If booCalcWeeks Then
      lngDiffWeeks = Abs(DateDiff("w", Date1, Date2)) - _
              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
      Date1 = DateAdd("ww", lngDiffWeeks, Date1)
   End If

   If booCalcDays Then
      lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
      Date1 = DateAdd("d", lngDiffDays, Date1)
   End If

   If booCalcHours Then
      lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
              IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
      Date1 = DateAdd("h", lngDiffHours, Date1)
   End If

   If booCalcMinutes Then
      lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
              IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
      Date1 = DateAdd("n", lngDiffMinutes, Date1)
   End If

   If booCalcSeconds Then
      lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
      Date1 = DateAdd("s", lngDiffSeconds, Date1)
   End If

    ' the format here
    varTemp = Interval
   If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
      varTemp = Replace(varTemp, "yyyy", "y")
      varTemp = Replace(varTemp, "y", lngDiffYears)
   End If

   If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
      If booCalcMonths Then
         varTemp = Replace(varTemp, "m", lngDiffMonths)
      End If
   End If

   If booCalcWeeks And (lngDiffWeeks > 0 Or ShowZero) Then
      If booCalcWeeks Then
         varTemp = Replace(varTemp, "ww", "w")
         varTemp = Replace(varTemp, "w", lngDiffWeeks)
      End If
   End If

   If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
      If booCalcDays Then
         varTemp = Replace(varTemp, "dd", "d")
         varTemp = Replace(varTemp, "d", lngDiffDays)
      End If
   End If
    
   If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
      If booCalcHours Then
         sFormat = String(NumChar(varTemp, "h"), "0")
         varTemp = Replace(varTemp, "hh", "h")
         varTemp = Replace(varTemp, "h", Format(lngDiffHours, sFormat))
      End If
   End If

   If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
      If booCalcMinutes Then
         sFormat = String(NumChar(varTemp, "n"), "0")
         varTemp = Replace(varTemp, "nn", "n")
         varTemp = Replace(varTemp, "n", Format(lngDiffMinutes, sFormat))
      End If
   End If

   If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
      If booCalcSeconds Then
         sFormat = String(NumChar(varTemp, "s"), "0")
         varTemp = Replace(varTemp, "ss", "s")
         varTemp = Replace(varTemp, "s", Format(lngDiffSeconds, sFormat))
      End If
   End If

   If booSwapped Then
      varTemp = "-" & varTemp
   End If

   Diff3Dates = Trim$(varTemp)

End_Diff2Dates:
   Exit Function

Err_Diff2Dates:
   Resume End_Diff2Dates

End Function
'************** Code End *****************

' arnelgp
Private Function NumChar(ByVal sInput As String, ByVal sChar As String)
    Dim lngLen1 As Long
    Dim lngLen2 As Long
    lngLen1 = Len(sInput)
    sInput = Replace(sInput, sChar, "")
    lngLen2 = Len(sInput)
    NumChar = lngLen1 - lngLen2
End Function
 

isladogs

MVP / VIP
Local time
Today, 09:50
Joined
Jan 14, 2017
Messages
18,186
try this function i modified.
copy and paste on standard module.
can be called in query or as expression.
on your case:

=Diff3Dates("m/d hh:nn:ss", [Date/Time reported], [Date/Time resolved])

i think there is no bearing if you put vbMonday there, since
we are interested on the difference.

That must be the longest function I've ever seen to calculate time differences!
I can confirm it works & is quick.

Here is a MUCH shorter alternative that you may be able to use for the purpose.
NOTE: I've adapted existing code of mine originally intended for intervals of less than a day. It could be further simplified

Paste this function into a standard module:

Code:
Option Compare Database
Option Explicit

Dim dblStart As Double, dblEnd As Double, dblTimeTaken As Double
Dim dteStart As Date, dteEnd As Date
Dim strTimeTaken As String

Function CheckTimeTaken()

    dblStart = CDbl(dteStart)
    dblEnd = CDbl(dteEnd)
    
    dblTimeTaken = dblEnd - dblStart
    
    'Debug.Print dblStart, dblEnd, dblTimeTaken
    
    'This gives the time elapsed in days
    'For simplicity, convert to seconds
    dblTimeTaken = 86400 * dblTimeTaken
    
    'Debug.Print dblTimeTaken
    
    'now create a text string for the time elapsed
    Select Case dblTimeTaken
    
    Case Is < 60
        strTimeTaken = CInt(dblTimeTaken) & " seconds"
    
    Case Is < 3600
        strTimeTaken = CInt(dblTimeTaken \ 60) & " min " & CInt(dblTimeTaken Mod 60) & " sec"
        
    Case Is < 86400
        strTimeTaken = CInt(dblTimeTaken \ 3600) & " hr " & CInt((dblTimeTaken Mod 3600) \ 60) & " min " & CInt(dblTimeTaken Mod 60) & " sec"
        
    Case Is >= 86400
       ' strTimeTaken = "More than 24 hours!"
        strTimeTaken = CInt(dblTimeTaken \ 86400) & " day " & CInt((dblTimeTaken Mod 86400) \ 3600) & " hr " & CInt((dblTimeTaken Mod 3600) \ 60) & " min " & CInt(dblTimeTaken Mod 60) & " sec"
    
    Case Else
        strTimeTaken = "Not known"
        
    End Select

End Function

Now use a procedure like this to calculate the difference for two selected dates/times

Code:
Sub GetTimeTaken()

'example values 
    dteStart = #10/5/2017 9:13:04 AM#
    dteEnd = #1/7/2018 11:34:27 PM#
    
    CheckTimeTaken
    
    Debug.Print "Time taken = " & strTimeTaken

End Sub

The above example gives this result
Time taken = 94 day 14 hr 21 min 23 sec
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:50
Joined
May 7, 2009
Messages
19,169
Yes long, the only benifit is the user provide the format the function will return.
 

isladogs

MVP / VIP
Local time
Today, 09:50
Joined
Jan 14, 2017
Messages
18,186
Hi arnel
Yes I know you were matching the format the OP suggested ... but its an odd format !!

This was the example I used to test your code:

Code:
Sub TestCode()
    Debug.Print Diff3Dates("m/d hh:nn:ss", #1/1/2017 12:54:56 PM#, #1/7/2018 11:34:15 PM#)
End Sub

This is the output for that example:
Code:
12/6 10:39:19

I'm not sure why anyone would want 12 months & 6 days written like that but its their choice

I assume it manages leap years OK but didn't test that
 

12asd

Registered User.
Local time
Today, 09:50
Joined
Dec 11, 2017
Messages
15
Hi arnel
Yes I know you were matching the format the OP suggested ... but its an odd format !!

This was the example I used to test your code:

Code:
Sub TestCode()
    Debug.Print Diff3Dates("m/d hh:nn:ss", #1/1/2017 12:54:56 PM#, #1/7/2018 11:34:15 PM#)
End Sub

This is the output for that example:
Code:
12/6 10:39:19

I'm not sure why anyone would want 12 months & 6 days written like that but its their choice

I assume it manages leap years OK but didn't test that

OK, well:
1. The times are written without the AM/PM
2. Not sure what Diff3Dates() means, besides it is not being done with vba but the simple expression builder.
3. I'm not entirely sure how the interval works, so the 'm/d hh:nn:ss' would be a test sort of thing.
 

12asd

Registered User.
Local time
Today, 09:50
Joined
Dec 11, 2017
Messages
15
arnelgp and ridders, I'm not looking for a whole load of vba, I just want to use a simple expression involving DateDiff() like what I had in the original post.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:50
Joined
May 7, 2009
Messages
19,169
It is correct see your format, there is no year on it just month and day and the hiurs. What you input that you get. There is ni ampm on the function, yet Todo.
 

isladogs

MVP / VIP
Local time
Today, 09:50
Joined
Jan 14, 2017
Messages
18,186
arnelgp and ridders, I'm not looking for a whole load of vba, I just want to use a simple expression involving DateDiff() like what I had in the original post.

There is no simple expression available for what you requested
That's why both arnel & I gave you complex functions


As I said before:
You could either do 4 separate DateDiff items ...
or just use 's' to get the value in seconds then do a further calculation to convert that to the format you want but that will have issues with number of days in each month

To build on what Minty wrote:
Hours:
Code:
=DateDiff("h",[Date/time reported],[Date/time resolved],vbMonday)

Days:
Code:
=DateDiff("d",[Date/time reported],[Date/time resolved],vbMonday)

Months:
Code:
=DateDiff("m",[Date/time reported],[Date/time resolved],vbMonday)

Years:
Code:
=DateDiff("yyyy",[Date/time reported],[Date/time resolved],vbMonday)

Seconds:
Code:
=DateDiff("s",[Date/time reported],[Date/time resolved],vbMonday)

etc....

Here is the full list available:
Code:
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
 

Mark_

Longboard on the internet
Local time
Today, 02:50
Joined
Sep 12, 2017
Messages
2,111
@OP,

Why would you NOT want to learn VB for use with access? This would be similar to asking "How can I paint my house" but then saying "Oh, no sprayer, no brush, and no roller".
 

12asd

Registered User.
Local time
Today, 09:50
Joined
Dec 11, 2017
Messages
15
@OP,

Why would you NOT want to learn VB for use with access? This would be similar to asking "How can I paint my house" but then saying "Oh, no sprayer, no brush, and no roller".

Because what I'm doing this for doesn't require us to learn vba, it is optional to use vba for forms, but we usually do append queries or expressions.

In the query I got it to work and managed to output the number of seconds.
 

Users who are viewing this thread

Top Bottom