I think I have looked at this too long!!! If "tblJobs.ServiceTypeID" is other then 1 or 7 it still returns 3%..... wheres my error!?!?!?!? :confused:
Commission: IIf([JobInvoicePaid]=True And tblJobs.ServiceTypeID=1 Or 7,(([ContractPrice])-Nz([JobSubAmount]))*0.03,([ContractPrice])-Nz([JobSubAmount])*0.05)

Thanks

I think I have looked at this too long!!! If "tblJobs.ServiceTypeID" is other then 1 or 7 it still returns 3%..... wheres my error!?!?!?!? :confused:
Commission: IIf([JobInvoicePaid]=True And tblJobs.ServiceTypeID=1 Or 7,(([ContractPrice])-Nz([JobSubAmount]))*0.03,([ContractPrice])-Nz([JobSubAmount])*0.05)

Thanks

Error in your condition.

Should be something like

Commission: IIf([JobInvoicePaid]=True And (tblJobs.ServiceTypeID=1 Or tblJobs.ServiceTypeID=7),(([ContractPrice])-Nz([JobSubAmount]))*0.03,([ContractPrice])-Nz([JobSubAmount])*0.05)

The Or 7 in your version was always giving TRUE

IIf([JobInvoicePaid]= True, IIf(tblJobs.ServiceTypeID = 1,(([ContractPrice])-Nz([JobSubAmount]))*0.03, IIf(tblJobs.ServiceTypeID = 7, (([ContractPrice])-Nz([JobSubAmount]))*0.03, ([ContractPrice])-Nz([JobSubAmount])*0.05)))

I think I got that right.

Or you can try:

IIf([JobInvoicePaid]=True And (tblJobs.ServiceTypeID=1 Or tblJobs.ServiceTypeID=7),(([ContractPrice])-Nz([JobSubAmount]))*0.03,([ContractPrice])-Nz([JobSubAmount])*0.05)

You need to seperate the 1 or 7 into two statements.

Either way should work, I think.

Got it...... Thanks!

or you could have used the IN operator...
Commission: IIf([JobInvoicePaid]=True And tblJobs.ServiceTypeID In (1, 7),(([ContractPrice])-Nz([JobSubAmount]))*0.03,([ContractPrice])-Nz([JobSubAmount])*0.05)

I'll have to remember that one Craig. Never seen the "In" operator before.....

It occasionally comes in handy :)

Here's a m$ link about it: http://office.microsoft.com/en-us/access/HP010322431033.aspx

Craig, cool discovery!

CEH- There's also "Switch" or "Choose" function which gives you same functionality as VBA's Select Case for the queries. Much more readable and efficient than Iif

Banana,

I'd heard of/used switch but not 'Choose'. :) Another one to file away for future reference!

Can the list of items in the choose function be an array, or does it have to be an explicit list along the lines of "x","y","z"?

Can't remember & don't have Access; maybe look at the help file?

Hmm. Well, it doesn't really talk much about the list of arguments except to say "Variant expression containing one of the possible choices.". I'm guessing that means you can't use an array. Still, interesting to know about :)

Hi -

Click here http://news.devx.com/showthread.php?t=53353&page=3 for sample code using the Choose() function with a paramarray.

Bob

Ah...very nifty raskew. Thanks :)