how to deal with null time value in text box

[RedGoneWILD]

I have a query that retrieves an employees schedule for the week. There is a field for start time and a field for finish time. I then place those values in text boxes on a schedule form. Sunday through Saturday.

Long story short, I would like to subtract the finish time from the start time and then format it.


Example;
Start = 8:15AM
Finish = 5:32PM
Total Hours Worked (formatted to look like this): 9h 17m

I have this working right now. The problem is, when the text boxes are empty, it throws an exception. I have it working fine until it reaches a day with no entry. For instance, If Sunday and Monday both have start and finish time entrys in their respective text boxes, the label below it holding the hours worked is good. But then Tuesday the employee is off, and it throws an invalid type error. Wednesday, etc., there are entries, but it doesn't go any further.

Any ideas?

[June7]

Show the query and the expression for the calculation.

Maybe using Nz function will take care of this.
Nz(starttime,0)
Nz(finishtime,0)

[RedGoneWILD]

Here's my code that works. The problem is when I hit the button that calls the function, I get a "Type Mismatch" error if all 7 days aren't filled in with start times and end times, and it stops on the first null day.

To explain the code, basically if there is no quit time (employee may still be clocked in), then display "0h 0m" in the hours worked label. Otherwise, subtract the quit time from the start time (produces a time value, like 8:15AM) and then format the hours and minutes section of it. I can't figure out why its not just skipping the null days.

    Dim hrsW As Date
    If IsNull(Me.txtSunAcQuit.Value) Then
        Me.lblSunHrs.Caption = "0h 0m"
    Else
        hrsW = Me.txtSunAcStart.Value - Me.txtSunAcQuit.Value
        Me.lblSunHrs.Caption = Format(hrsW, "h") & "h " & Format(hrsW, "n") & "m"
    End If

[pbaldy]

So test first:

If IsDate(...) And IsDate(...) Then
  'do your thing
Else
  'they're not filled out
End If

[RedGoneWILD]

that worked thanks baldy

[pbaldy]

No problem.