#Error

[grogmi1]

Hi,

I have a query in my Access database which works out the duration of a job. The Query has 2 columns 'TIME IN' and 'TIME OUT' then there is a calculated column which calculates the duration of each job. The problem I have is that when there is no times entered into TIME IN or TIME OUT the duration field display #Error.

Is there anyway I can stop this from happening ?

Thanks in Advance

 

[bob fitz]

Can you show us the expression used for the calculation

 

[grogmi1]

Hi Bob,

Here is the expression used to calculate the job duration

'Duration: ElapsedTime([TIME IN],[TIME OUT])'

Thanks

 

[Cotty42]

You could check for Null, not sure how this would work in a query but something like:

If Not IsNull([TIME IN]) Or Not IsNull([TIME OUT]) Then
    'Duration: ElapsedTime([TIME IN],[TIME OUT])'
End If

 

[grogmi1]

Thanks guys, I'll play around with this and see if I can get It to work

 

[Cotty42]

I assume ElapsedTime is a Public function, in which case the following will return 00:00:00 if one time value is missing:

Public Function ElapsedTime(startTime, endTime)
    Dim Interval As Date
     
    ' Calculate the time interval.
    If IsNull(endTime) Or IsNull(startTime) Then
        Interval = vbBlank
    Else
        Interval = endTime - startTime
    End If
    
    ElapsedTime = Interval
 
End Function

Note! - startTime and endTime are not assigned as Date Variables