If result is negative then enter 0

G

Guest

Hi all:
I have a query with date worked, log in time, log out time, acthrs and
overtime. With the help of Steve Schapel I was able to convert these to
actual hours worked. Now I need to take the actual hours worked and if more
than 8 hrs put the diff in the overtime field and if the hours worked are
less than 8 than put in 0 in the overtime field. My expression so far is
[acthrs]-8.0, but if the act hrs are less than 8 I get a negative amount
which I don't want. How do I write the expression to limit the overtime to
numbers greater more than 8.
thanks
 
M

Marshall Barton

rascal said:
Hi all:
I have a query with date worked, log in time, log out time, acthrs and
overtime. With the help of Steve Schapel I was able to convert these to
actual hours worked. Now I need to take the actual hours worked and if more
than 8 hrs put the diff in the overtime field and if the hours worked are
less than 8 than put in 0 in the overtime field. My expression so far is
[acthrs]-8.0, but if the act hrs are less than 8 I get a negative amount
which I don't want. How do I write the expression to limit the overtime to
numbers greater more than 8.
Click to expand...


IIf(acthrs > 8, acthrs-8, 0)
 
J

JethroUK©

assuming noone works more than 15 hrs in a shift - you could use:

[acthrs] Mod 8
 
G

Guest

Thanks everyone for your suggestions. They work beautifully.
:)
John Spencer said:
IIF([acthrs]-8<0,0,[acthrs]-8)


rascal said:
Hi all:
I have a query with date worked, log in time, log out time, acthrs and
overtime. With the help of Steve Schapel I was able to convert these to
actual hours worked. Now I need to take the actual hours worked and if
more
than 8 hrs put the diff in the overtime field and if the hours worked are
less than 8 than put in 0 in the overtime field. My expression so far is
[acthrs]-8.0, but if the act hrs are less than 8 I get a negative amount
which I don't want. How do I write the expression to limit the overtime
to
numbers greater more than 8.
thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hourly weekly work hours 1
Dsum question getting running total in a query 1
Substituting result value in a report? 4
Converting a non-standard table 1
Help with General Approach 2
Splitting a record in two 2
Time to and then from Midnight 1
The query is too complex or can't open anymore databases 11

Top