Negative Numbers

[Melinda]

I have an overtime database in which once a year I need to roll back overtime
hours by the lowest employees' total. I have an overtime code to calculate
different types of overtime. Hours worked and hours refused and they are
totaled together. We can have employees who have more refused hours than
worked hours and when I go to subtract they result in a negative number.
How do I switch those negative numbers to a "0" in an append query. I have
been struggling with this for some time.

INSERT INTO tblhours ( TMSEmployeeNo, EmployeeName, OvertimeCodes,
HoursWorked )
SELECT tblEmployees.Tmsemployeeno, tblEmployees.EmployeeName, "rollback" AS
y, -[enter the number of hours] AS z
FROM tblEmployees INNER JOIN tblHours ON tblEmployees.Tmsemployeeno =
tblHours.TMSEmployeeNo
GROUP BY tblEmployees.Tmsemployeeno, tblEmployees.EmployeeName, "rollback",
-[enter the number of hours], tblEmployees.ClassNo, tblEmployees.OutPost
HAVING (((tblEmployees.ClassNo)="16512") AND ((tblEmployees.OutPost)>-1))
ORDER BY tblEmployees.ClassNo, Sum(tblHours.HoursWorked);

I have to rollback by class numbers. Any help would be appreciated.

 

[Ofer Cohen]

Try

IIf(CalculatedValue < 0 , 0 ,CalculatedValue )

When CalculatedValue is the calculated field or formula

 

[Melinda]

Sorry---

I just looked back at the post, where would I put this Iif statement at?

Try

IIf(CalculatedValue < 0 , 0 ,CalculatedValue )

When CalculatedValue is the calculated field or formula

--
Good Luck
BS"D

I have an overtime database in which once a year I need to roll back overtime
hours by the lowest employees' total. I have an overtime code to calculate
different types of overtime. Hours worked and hours refused and they are
totaled together. We can have employees who have more refused hours than
worked hours and when I go to subtract they result in a negative number.
How do I switch those negative numbers to a "0" in an append query. I have
been struggling with this for some time.

INSERT INTO tblhours ( TMSEmployeeNo, EmployeeName, OvertimeCodes,
HoursWorked )
SELECT tblEmployees.Tmsemployeeno, tblEmployees.EmployeeName, "rollback" AS
y, -[enter the number of hours] AS z
FROM tblEmployees INNER JOIN tblHours ON tblEmployees.Tmsemployeeno =
tblHours.TMSEmployeeNo
GROUP BY tblEmployees.Tmsemployeeno, tblEmployees.EmployeeName, "rollback",
-[enter the number of hours], tblEmployees.ClassNo, tblEmployees.OutPost
HAVING (((tblEmployees.ClassNo)="16512") AND ((tblEmployees.OutPost)>-1))
ORDER BY tblEmployees.ClassNo, Sum(tblHours.HoursWorked);

I have to rollback by class numbers. Any help would be appreciated.