Negative Numbers

M

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

Ofer Cohen

Try

IIf(CalculatedValue < 0 , 0 ,CalculatedValue )

When CalculatedValue is the calculated field or formula
 
M

Melinda

Sorry---

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

Ofer Cohen said:
Try

IIf(CalculatedValue < 0 , 0 ,CalculatedValue )

When CalculatedValue is the calculated field or formula

--
Good Luck
BS"D


Melinda said:
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.
 

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

SQL 0
Append Queries 0
Append Query 0
Queries? 9
Update Query 2
Update Queries 4
Cross tab query I think but do not know how to do it. 0
If result is negative then enter 0 5

Top