Nz? IIf? Trouble with dates

[Ally]

I have some code to sort out the datediff between times including dates. I'm calling it from a query. If the date and time fields are empty the time field I'm calculting gives #Error.

The code is:

Function TimeCount(a As Date, b As Date, c As Date, d As Date)
Dim x As Integer, y As Integer
    
 y = DateDiff("d", a, b)
 
    If y = 0 Then
        'TimeCount = minutes: time2-time1
        x = DateDiff("n", c, d)
    
    ElseIf y = 1 Then
        'TimeCount = minutes: time1-23:59:59 + minutes: time2 +1 minute
        x = DateDiff("n", c, "23:59:59") + DateDiff("n", "0:0:01", d) + 1
                
    Else
        x = DateDiff("n", c, "23:59:59") + DateDiff("n", "0:0:01", d) + 1
        GoTo 10
        
    End If

    TimeCount = x \ 60 & ":" & Format(x Mod 60, "00")
    Exit Function

10: TimeCount = (x + (y * 1440) - 1440) \ 60 & ":" & Format(x Mod 60, "00")
End Function

In the query I've got:
Delay: TimeCount([dischdecdate],[dischdate],[dischdectime], [dischtime])

I've tried variations of IIf and Nz to try and display "" in the field, eg:

Delay1: IIf(nz(TimeCount([dischdecdate],[dischdate],[dischdectime],[dischtime])),TimeCount([dischdecdate],[dischdate],[dischdectime],[dischtime]),"")

but still get #Error. Anyone got any ideas please?

 

[Fizzio]

The first thing to notice is that your IIf statement is not correct. With an IIf you check for a condition and then return a true or false value. in your IIf, you are not checking for a condition, but merely converting a null to zero. To truly check for a null, your IIf would need to be IIf(isnull(timecount(variables)),timecount(variables),"") but I do not think this will solve your problem.

Obviously, for your function to return a correct value, a,b,c and d must be populated. In your function you could check for null values in the function arguments and return a specific value if any of them are null eg

Function Timecount(a,b,c,d)
if isnull(a) or isnull(b) or isnull(c) or isnull(d) then
timecount = ""
end function
end if

Rich advocates the use of Not IsDate() rather than IsNull as this gives more accurate processing of null date/time fields.

hth

 

[Ally]

Thank you - This is one of the things I had tried, but unfortunately, it doesn't work either. I just tried Not IsDate too but that still returned #Error.

 

[Fizzio]

The reason is that you are sending a null argument to the function. How you can get around this is using a Nz function within your function call ie

Delay: TimeCount(nz([dischdecdate]),Nz([dischdate]),Nz([dischdectime]), Nz([dischtime]))

BUT this will treat every null as 0! so....

Function Timecount....
Dim .....

If a = 0 Or b = 0 Or c = 0 Or d = 0 Then
TimeCount = ""
Exit Function
End If

should fix it

 

[Ally]

Excellent Fizz - you're wonderful. Col and I were both having a bash at it and neither of us were getting anywhere.

 

[Fizzio]

Nae Bother

 

[Ally]

A little problem as a spin-off from this ...

I'm now getting an overflow problem when I enter the dates because the first date is then trying to calculate with a null value. Does anyone know how to get over this please?

 

[Fizzio]

Yep, I got that as well when I tested it first but I assumed that the delay time would not be that long (naive I know - should know better working in the NHS!)

try changing

Dim x As Integer, y As Integer

to

Dim x As Long, y As Long

and see what that does.

 

[Ally]

Well the error's stopped, but the value in the time field is: -907512:00 !!!

 

[Fizzio]

what values do you have for a,b,c,d in your Db?

 

[Ally]

11/07/03 in a, then nothing yet in b, c or d.

 

[Fizzio]

Ally, I got it to work fine - keeping a blank delay if the other fields are empty.

 

[Rich]

Did you try
Function TimeCount(a , b, c , d As Variant)

 

[Ally]

Thank you so much Kris - that works a treat. (I did try what you suggested Rich, but that didn't work).

 

[Fizzio]

No probs, have a good weekend