Nested IIf statements

[debswebs]

I am trying to create a calculated query using a nested IIf statement to determine what rate to charge contractor members based on these three criteria:

• Project Let Date >= 01-01-2011
• ProjectType is = PCCP
• ContractorType IS NOT = "Specialty"

I can get the following expression to work, but not sure how to add the second and third conditions:

CPTech: IIf([LetDate]>#1/1/2011#,([Qty]*0.01),0)

Can someone help me out?

 

[pbaldy]

You don't need it nested, just multiple conditions:

CPTech: IIf([LetDate]>#1/1/2011# AND ProjectType = "PCCP" AND ContractorType <> "Specialty",([Qty]*0.01),0)

Having said that, I probably wouldn't code business logic into the application like that.

 

[debswebs]

Being new to ACCESS, where would you use it?

 

[mahenkj2]

Try This:

SELECT Table4.ID, Table4.LetDate, Table4.ProjectType, Table4.ContractorType, Table4.Qty, IIf([LetDate]>#1/1/2011#,([Qty]*0.01),0) AS CPTech
FROM Table4
WHERE (((Table4.ProjectType)="PCCP") AND ((Table4.ContractorType)="Speciality"));

Replace field and table name appropriately.

regards.


Edit:Sorry Paul, I just saw you already replied. Excuse me.

 

[pbaldy]

@debswebs: I'm not sure exactly what you're doing, but in general I'd try to have a table with the options and the value to use. What happens when next week the powers-that-be decide that project type "ABCD" will also qualify, or contractor type "Whatever" is also excluded? As you have it, you'll have to find anyplace in the db where you've got this type of formula and change it. I'd rather have a table where users can maintain things, and have the application flow without design changes. Not sure it's applicable to you, just throwing out some thoughts.

@mahenkj2: no problem, it happens often that two people are typing at the same time. Unlike Access, there's no record locking feature here!

 

[debswebs]

Thanks, mahenjk2, using "and" statements worked.

@pbaldy - we about 40 contractor members. Each contractor member has projects, and each project has many pay items. We calculate three difference assessment fees on different criteria such as the type contractor, the type project, type pay item and when the project was let. From what I've learned in Access, you should not calculate in tables - so I an creating queries with calculated rates for each of the three types (SE, NATL, and CPTECH). . . . since our assessment rates are so complicated, and I'm new to access, it seems easiest to do it in a query???? . . .

Thank you both for your help!!!