#Error with Function in query (1 Viewer)

bradles

New member
Local time
Tomorrow, 06:24
Joined
Oct 20, 2006
Messages
4
Hi All,

I made a function to calculate the difference in hours between two times.

Code:
Function TimeDiff(Time1 As String, Time2 As String) As Double
'This function is used to calculate the difference in hours between two times.
'It is capable of handling a finish time that is after midnight.
    
    TimeDiff = 0
    
    If Not IsNull(Time1) And Not IsNull(Time2) Then
            
        If CDate(Time1) > CDate(Time2) Then
            TimeDiff = ((CDate(Time2) - CDate(Time1)) + 1) * 24
        Else
            TimeDiff = (CDate(Time2) - CDate(Time1)) * 24
        End If
            
    End If

End Function
I have a query where there are 2 sets of times in fields (Start1, Finish1, Start2, Finish2).
When I add a calculated column with the following expression:
Code:
Expr1: TimeDiff(Start1,Finish1) + TimeDiff(Start2,Finish2)
it calculates in the query correctly.

However, when I made a new function that incorporated this function, it gave me the #error result in the calculated field.
Code:
Function OrdinaryHours(Status As String, Start1 As String, Finish1 As String, Optional Start2 As String, Optional Finish2 As String) As Double
    
    'Initialise
    OrdinaryHours = 0
    
    'If employee is NOT a casual
    If Not Status = "CT" Then
        
        If Not IsMissing(Start2) And Not IsMissing(Finish2) Then
            OrdinaryHours = TimeDiff(Start1, Finish1) + TimeDiff(Start2, Finish2)
        Else
            OrdinaryHours = TimeDiff(Start1, Finish1)
        End If
        
    End If
    
    
End Function
Does anyone know why this would #Error. I have the TimeDiff function initialising to = 0 before doing anything...I am stumped why it returns #Error when it should return 0 in the case of something going wrong.

Brad
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:24
Joined
Sep 12, 2006
Messages
15,614
incidentally, if you are looking to find the difference between two things irrespective of the order, you can use abs (absolute)

TimeDiff = 0

If Not IsNull(Time1) And Not IsNull(Time2) Then
timediff = abs( cdate(Time1) - CDate(Time2) ) * 24
End If

i take it this function is in the definition row of a query, somewhere. Is the function returning error on EVERY line, or just some lines

I think you may get an error in your function if any of your parameters are blank, because you can't pass NULL into a string variable.

in the initial call, try wrapping each parameter in an nz. This will change any null into a blank string

ie ordinaryhours(status,nz(starttime1,""),nz(starttime2,"") etc

however because you won't then have nulls inside your functions, you will need to change the test for null, to testing for "" (or vbnullstring which is the samething, but you can't use that inside the query itself)

Rather than using the query you can test the function call in vb

in a module have

sub tryme
dim mytime as double

mytime = ordinaryhours(various test parameters)
msgbox(mytime)
end sub

just put the cursor at the start of the sub, and press run - it will just run the sub this once. you can see what happens if various parameters are set or blank etc
 

bradles

New member
Local time
Tomorrow, 06:24
Joined
Oct 20, 2006
Messages
4
Ah-ha.
Thanks Gemma. I changed the functions to pass to a variant instead of a string. So far that function is now working. Now I need to translate this to the other functions I have.

Fingers crossed.

Thanks again.

Brad
 

bradles

New member
Local time
Tomorrow, 06:24
Joined
Oct 20, 2006
Messages
4
gemma-the-husky said:
incidentally, if you are looking to find the difference between two things irrespective of the order, you can use abs (absolute)

TimeDiff = 0

If Not IsNull(Time1) And Not IsNull(Time2) Then
timediff = abs( cdate(Time1) - CDate(Time2) ) * 24
End If
I tried this function Gemma but found it didn't handle times that cross midnight. For example, 22:00 - 06:00 should return 8 hours, however it returned 16 hours.

I don't know if there is a way that it can be adapted to work...I'd like to see it though, especially if it saves a few more lines of code.

Thanks for your help.

Brad
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:24
Joined
Sep 12, 2006
Messages
15,614
out of interest, then - how are you storing your times?

a normal datetime type stores a date as well, even if you don't display it.

perhaps you need to go back to your original function if the order of the times is significant.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:24
Joined
Aug 30, 2003
Messages
36,118
This works for 2 times, even across midnight:

Format([StartTime] -1 -[EndTime], "Short Time")
 

Users who are viewing this thread

Top Bottom