TimeStamp Calculations are wrong. Date Diff

[Guest]

For some reason, my time calculations are wrong when i run the queiries. if
it is 7am to 9pm, the calculations are wrong. But if its from 3 am to 11 am
or 1 pm to 9 pm, its correct. Do I have to tell it that its a 12 hour time?
here is the formula that I use:

Hours: ((IIf([ClockIn]=True,Null,DateDiff("h",Nz((SELECT Max(Q.[TimeStamp])
FROM TimeClock AS Q WHERE Q.ClockIn = True AND Q.TimeStamp <
T.[TimeStamp]),0),[T].[TimeStamp])))))

 

[Guest]

What is the format of your TimeStamp field? In answer to your question, yes
you do need to tell it that your using a 12 hour time, but putting in the
am/pm indicator should do this. Using Access 2000 I have tried:

DateDiff("h", "7:00", "21:00")
DateDiff("h", "7:00", "9:00 pm")
DateDiff("h", "7:00", "9:00pm")
DateDiff("h", "7:00", "9:00 p")
DateDiff("h", "7:00", "9:00p")
DateDiff("h", "7:00", "9p")

and they all give the same answer (14). From testing various other
permutations and formats, it appears that Access will by default assume am
unless you either specify p or pm as part of the format or use a 24 hour
format. The 24 hour format overrides any suffix (it reads "21:00 am" as 9:00
pm)