More Complex Calc in Query

[Michael]

Hi Folks - I need some help with a query. Here's the business logic:

If HoursWorked < 4, then HoursWorked = 4
else
If HoursWorked > 4 and <= 6, then HoursWorked = 6
else
If HoursWorked > 6 and <= 8, then HoursWorked = 8
else
If HoursWorked > 8 then HoursWorked = HoursWorked

Should I use an IIF statement, or should I use a public function? Either
way, I'll need some help in creating the IIF statement or setting up the
query to use a public function and the VBA code.

Thoughts?

Thanks.

Mike

 

[Bob Barrows]

Hi Folks - I need some help with a query. Here's the business logic:

If HoursWorked < 4, then HoursWorked = 4
else
If HoursWorked > 4 and <= 6, then HoursWorked = 6

Errr ... what if HoursWorked is exactly 4? I assume you meant:
If HoursWorked <= 4, then HoursWorked = 4

else
If HoursWorked > 6 and <= 8, then HoursWorked = 8
else
If HoursWorked > 8 then HoursWorked = HoursWorked

Should I use an IIF statement, or should I use a public function?

It depends. Here's a nested iif version:
Iif(HoursWorked <= 4,4,Iif(Hoursworked <=
6,6,Iif(HoursWorked<=8,8,HoursWorked)))