Using DateDiff To Calculate Dates with Nulls

[databasegirl]

I am trying to calculate the difference between two dates in the query, see below:

Time Diff Recd Vs Entry: Round(nz(DateDiff("s",[Received Time],[Entry Time])/60,0),2)

The problem is that not all of the fields for Received Time have dates in them, therefore I am getting an ERROR# in the result. I thought the nz function would handle null values. Any ideas? Thanks!

 

[PHV]

What about this ?
Time Diff Recd Vs Entry: Round(DateDiff("s",Nz([Received Time],[Entry Time]),[Entry Time])/60,2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MichaelRed]

Please review the code snippet below. I have attempted to "de-compose" your expression but the functions / arguments seem to be mis matched.

I have generated a brief function to expresss this de-composition. My first variable dtDiff is just the replacement of your inner function, with the added R Paren to balance the parens.

The datediff part seems to be OK, with the exception of needing a R Paren so it applies to JUST the "[Receieved Time]" argument?



This however invalidates the parens balance. So additional adjustments are necesary.

From inspection, it appears that the second "[Entry Time]" argument is beyond the needs, of the overall function, appearing to be the If True part of an IIF? In any case, I removed it since there is little indication of it's purpose.

So, my second expression (rndDtDiff) appears to be the results of the removal of this second use of the [Entry Time] argument and returns the 'desired' results.

Id DO suspect the second use of the [Entry Time] Argument is intended as the If True results, however this seems just as spurious as a random number?

If the intent was to use the second of the [Entry time] arguments as the false part of an IIF statement, it MIGHT look something like:

? IIF(Nz(dtStart), dtEnd,  Round(dateDiff("s", dtStart, dtEnd)/ 60, 2))

Public Function basTestDateDiffRound(dtStart As Variant, dtEnd As Variant)

    'Round(DateDiff("s",Nz([Received Time],[Entry Time]),[Entry Time])/60,2)

    Dim dtDiff As Double
    Dim rndDtDiff As Double

    dtDiff = DateDiff("s", Nz(dtStart[COLOR=red][b])[/b][/color], dtEnd)
    Debug.Print dtDiff

    rndDtDiff = Round((dtDiff / 60), 2)
    Debug.Print rndDtDiff

End Function

MichaelRed

 

[databasegirl]

Thanks for the responses. I tried PHVs response and still received errors.

I'm not understanding Micheal's response. I created a public function but how am I to use this in my query? Also, are those two different possible solutions to my question?

 

[PHV]

Does [Entry Time] hold Null values too ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[databasegirl]

Yes, Entry Time could have null values as well.

 

[PHV]

Time Diff Recd Vs Entry: IIf([Received Time] Is Null OR [Entry Time] Is Null,0,Round(DateDiff("s",[Received Time],[Entry Time])/60,2))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[databasegirl]

You are AWESOME! It works like a charm. Thanks a lot!