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!

 

[Pat Hartman]

When you turn dates/times into strings, you can't do proper arithmetic with them. The whole point of a date/time data type is that Access can do simple math on the fields and return the answer to you in whatever units you want. If you use DateDiff() as Wayne suggested, you will get the correct answer.

 

[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?!?!?!

 

[Pat Hartman]

1. Do NOT format your dates before using them in calculations!! Formatting turns them into text strings.

2. Whether you get positive or negative result from DateDiff() is totally dependent on which date you put first.

A - B = -15
B - A = 15

 

[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.