IIF Statement Not Working

[Jsha]

I have been trying to get this conditional calculation working in a query for the past couple days. I wanted to know if I am doing something wrong here and if so what?

The problem is this, if a person works 10 hours a day. 8 of those hours they were doing work for "Phase1" and the other 2 hours they were doing work for "Phase2". Now, there are two seperate multipliers that I need to use for each Phase respectively. So the formula is as follows, iif(Phase = "Phase1", (hourlyrate * hours) * Phase1Multiplier, (hourlyrate * hours) * Phase2Multiplier)

This formula works when I input it into a report in an unbound field, but because I need to sum of the results for Phase 1 and Phase 2, I thought it would be easier if I built it into the query.

Please help.

 

[SgtJarrow]

Something like:

=IIF([Hours]<=8,[Hours]*[HourlyRate]*[Phase1Multiplier],(8*[HourlyRate]*[Phase1Miltiplier])+(([Hours]-8)*[HourlyRate]*[Phase2Multiplier]))

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Jsha]

Thank you for the reply, but it is more related to the Phase than anything. When the hours are input, the user tells the database where to apply the hours. So, it would be 2 hours in Phase2 and 8 hours in Phase1, which would in turn give the employee 10hours for the day.

 

[SgtJarrow]

I am not too sure I follow your logic then...Can you post some sample data and expected results?

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[lespaul]

what's the rest of the query too?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins