Why is AM greater than PM

[dbalargin]

I am calculating the difference between 3:30 PM - 12:00 AM.
This is a work shift and I need to know hours worked.
Here is my statement:

IIf([monday_start]<[monday_end],Format(DatePart("h",[monday_start]-[monday_end])+DatePart("n",[monday_start]-[monday_end])/60,"#.00",0)

End result is 0.
WHY???

I've tried using 11:59 PM vs 12:00 AM

 

[dbalargin]

correction:
IIf([monday_start]<[monday_end],Format(DatePart("h",[monday_end]-[monday_start])+DatePart("n",[monday_end]-[monday_start])/60,"#.00",0)

 

[WayneRyan]

dba,

Why don't you use DateDiff on a Full date/time control.

If you have 8/13/03 11:59:00 PM and you add one minute to
it you have 8/14/03

It has an implied 12:00:00 AM (00:00:00 AM?). If Access
keeps track of it internally (through its long integer)
it does OK.

If you just supply a short date to a long date format and
do calculations it defaults to 12:00:00 AM (00:00:00 AM?).
Which gives problems with the between operator in queries.

I don't know if I really answered anything, but I think
I'm more confused than when I started. Maybe someone
can shed some light on this. I noticed no response so I
thought I'd play around with the debugger and take a shot.

Wayne

 

[Vassago]

Add a date field into the table if you can, or create an Iif Statement to specify that 12:00:00 AM = monday_end + one day at 12:00:00 AM. Access thinks your midnight time is the same day, when actually it is the next.

Good Luck!

 

[dbalargin]

I use datediff or datepart. I enter 3:30 pm 12:00 AM. With datediff I get -15. When I use datepart I get 15.
What is going on?!?!?!

 

[raskew]

Take a look here for a similar discussion: http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52670&highlight=returns+hours

 

[Vassago]

As I mentioned, your problem is that you are not including dates in the calculation, only time. Place a date to both times, the midnight one being one day ahead of 3:30. This should make it calculate correctly.