Thread: Nesting IIF's - "Wrong number of arguments"

1. Registered User
Join Date
Jul 2009
Posts
3

Unanswered: Nesting IIF's - "Wrong number of arguments"

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)))

Cheers, prior.

**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...
Last edited by Beamsbox; 07-31-09 at 15:39.

2. Registered User
Join Date
Jun 2007
Location
Maitland NSW,Australia
Posts
401
Instead of using the between I would use the following:

costFlightRetardant:IIf(Sum([retardantTWF])<200000,IIf(Sum([retardantTWF])<100000,[retardantTWF]*[retardantTWF_to_100000],[retardantTWF]*[retardantTWF_100001_to_200000],[retardantTWF]*[retardantTWF_200001_plus])

3. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
http://www.dbforums.com/microsoft-ac...ice-range.html

Same issue, more or less. Give it a whirl.

4. Registered User
Join Date
Jul 2009
Posts
3

Incremental pricing structure...?

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.

I think we're getting closer, though.

5. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
You can solve that issue by storing the specific price along with your range. No biggie.

6. Registered User
Join Date
Jul 2009
Posts
3
Hey thanks for your help Teddy. I must admit that you've totally lost me unfortunately.

In the thread link that you noted (Count With Date Range and Price Range) I'm not sure what the final conclusion was... did he use this coding:

SELECT COUNT(*) AS [Count Of tblCARETSData]
FROM 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.

Cheers.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•