Incorrect result from Dmax statement

[Derek]

Hi guys

I hope anyone can solve my problem.
IF i WRITE THE FOLLOWING FUNCTION THEN IT GIVES ME
Starttime=10:07:45
Finishtime=10:06:38
dtmtotaltime=00:01:07
Here we are finding the maximum finishtime in the table where name=anyname.

Public Function Func1(AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
 Dim dtmLower As Date
    Dim dtmUpper As Date
    dtmUpper = AnyStartTime
     MsgBox AnyDate
    dtmLower = nz(DMax("Finishtime", "tblbatchdetails", "Name='" & AnyPerson & "'"))
        Dim dtmtotaltime As Date
    dtmtotaltime = dtmUpper - dtmLower
        Func1 = dtmtotaltime
       End Function

But in the following function it gives me the following result:
Starttime=10:07:45
Finishtime=00:00:00
dtmtotaltime=10:07:45

I think there is some problem with Date condition in Dmax statement. Because of Date condition it doesn't capture the max finishtime in the table.

Public Function Func2( AnyPerson As String, AnyStartTime As Date, AnyEndTime As Date) As String
 Dim dtmLower As Date
    Dim dtmUpper As Date
    dtmUpper = AnyStartTime
      MsgBox AnyDate
    dtmLower = nz(DMax("Finishtime", "tblbatchdetails", "Name='" & AnyPerson & "' and Date1=#"& format(Date,"dd/mm/yyyy") &"#))

      Dim dtmtotaltime As Date
    dtmtotaltime = dtmUpper - dtmLower
        Func2 = dtmtotaltime
       End Function

If I remove the Date condition then it gives me the right result but it is must to include date condition as we have to find out the max finishtime for today for any name.

Please find enclosed the database in access. Actually my front end is excel and backend is Access for this project.

Thanks in advance for any help!

Derek

 

[gemma-the-husky]

are you sure you dont have someone entered today with a blank date (or more likely a date of 0 - 30/11/1899) - then a returned value of 0 might be correct, in terms of the query.

or maybe (more likely) there are no entries matching the searched name - so yuor nz is making the returned value 0.

 

[namliam]

Why use the DMax at all? If you have "any person" you should know the relative start and end times already why not 'simply' calculate it on the spot?

 

[namliam]

Oh and also....
Date1=#"& format(Date,"dd/mm/yyyy") &"#))

That will break because access 'requires' US date formats between the ##
Either use US or ISO dates, or use Dateserial function:
Date1=#"& format(Date,"mm/dd/yyyy") &"#)) ' US
Date1=#"& format(Date,"yyyy/mm/dd") &"#)) ' ISO
Date1=dateserial("& format(Date,"yyyy,mm,dd") &"))) ' dateserial

Good hunting !

 

[Derek]

Thanks a lot namiliam. Dateserial function worked perfectly fine.

 dtmLower = nz(DMax("Finishtime", "tblbatchdetails", "Name='" & AnyPerson & "' and Date1=dateserial(" & Format(Date, "yyyy,mm,dd") & ")"))

Thanks a lot.