Code is not giving the right result (1 Viewer)

[Derek]

My question is to find out the time difference between finish time of first record and start time of the next record for the same person as it will give us the exact time measurement how much time the user was free after inserting his first record and before starting a new record. This is elapsed time i am going to calc. I have written the following function to find out the elapsed time. but my problem is now when I insert a record then elapsed time is coming up same as that of start time of that record. But in actual the elapsed time should be:
Difference between finish time of the last saved record of the same person and start time of the new record the person going to insert in the table.

a = Timefunc1(Format(Date, "dd/mm/yyyy"), TextBox10.Value, TextBox8.Value, TextBox11.Value)
                         With rs
                    .AddNew
                    .Fields("Refno") = TextBox9.Value
                    .Fields("Date1") = Format(Date, "dd/mm/yyyy")
                    .Fields("Process1") = "Batch"
                    .Fields("Name") = TextBox10.Value
                    .Fields("Formfamily") = ComboBox6.Value
                    .Fields("Batchedby") = ComboBox5.Value
                    .Fields("Volume") = TextBox2.Value
                    .Fields("starttime") = TextBox8.Value
                    .Fields("Finishtime") = TextBox11.Value
                    .Fields("Elapsedtime") = a
                    .Update
                    End With

Public Function Timefunc1(AnyDate As Date, AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
    Dim dtmLower As Date
    Dim dtmUpper As Date
    dtmUpper = AnyStartTime
    dtmLower = nz(DMax("[Finishtime]", "[tblbatchdetails]", "[Name]='" & AnyPerson & "' And [Date1] = #" & AnyDate & "# And  [Finishtime] < #" & AnyStartTime & "#"), 0)
    
    Dim dtmtotaltime As Date
     dtmtotaltime = dtmLower - dtmUpper
        
        Timefunc1 = dtmtotaltime

end function

 

[Derek]

Hi folks

Now I have come to know that the statement DMax is not giving me the maximum finish time for the same person in the table. It gives 00:00:00 always.
Can anyone please have a look at Dmax statement and tell me why its not working as desired.

Thanks

 

[Guus2005]

I think you forgot to check for NULLs:

dtmLower = nz(DMax("[Finishtime]", "[tblbatchdetails]", "[Name]='" & AnyPerson & "' And [Date1] = #" & AnyDate & "# And  [Finishtime] < #" & AnyStartTime & "#" AND [FinishTime] Is Not Null), 0)

If DMax returns Null Nz converts it to 0 (the value you entered). Expecting a datetime value, it is implicitly converted to 00:00.

HTH