Unanswered: Nesting IIF's - "Wrong number of arguments"
I can't figure this out?!?... please help...
costFlightRetardant:IIf(Sum([retardantTWF])<100001,[retardantTWF]*[retardantTWF_to_100000],IIF(Sum([retardantTWF]) Between 100000 AND 200000,[retardantTWF]*[retardantTWF_100001_to_200000],IIF(Sum([retardantTWF])>=200001,[retardantTWF]*[retardantTWF_200001_plus],0)))
**Edit** I think this now works... but with another error, "does not include '-----' as part of an aggregate function"
What I'm trying to do...
I have a 3 prices for the same product. The price changes depending on the gallons used.
Up to and including 100,000 gal, is one price.
Between 100,001 and 199,000 gal is another price.
And anything equal to and above 200,000 gal is a third price...
I've tried to sum the retardantTWF field in a separate table, but I'm afraid that this approach will alter all the pricing for gallons below 200,000, once the sum crosses that line...
Thanks for the reply. I'll check that out tomorrow at work; makes sense and is less complex than my query.
I think though, after a bit more thought, that using this approach won't accomplish my goal. I think that this will run through the database and once the sum totals more than 200,000 all my entries are going to be multiplied by the third one of my prices.
I really need a formula that will calculate incremental pricing. So that the usage below 100,000 gallons is charged a particular price, the next increment (only) charged another price, and the third price applying to only anything over 200,000 gallons.
SELECT COUNT(*) AS [Count Of tblCARETSData]
WHERE ClosingDate >= #6/1/2009#
And ClosingDate <= #6/30/2009#
AND ClosePrice <= 199999
AND County = 'orange'
or did he use the RETS coding that you provided? Not sure I understand it correctly either. Would I need to try to count out the results of my 'sales' table into a query field for each price range, summing the results into the necessary ranges, and then using another query to multiply the results by prices...?
Originally Posted by Teddy
You can solve that issue by storing the specific price along with your range. No biggie.
Also, how would I store the pricing with the range? Do I need to use a specific table for just this, or could I enter this into the 'criteria' field.
Sorry for my ignorance, I'm just learning this program, it's the only one in the office suite I've been avoiding until now... grrr... what better way than putting together a gigantic database... truth is, I've learned a lot so far and I appreciate you sharing your time and assistance.