Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    3

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

    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 16:39.

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    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. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    http://www.dbforums.com/microsoft-ac...ice-range.html

    Same issue, more or less. Give it a whirl.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    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. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can solve that issue by storing the specific price along with your range. No biggie.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    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...?

    Quote 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
  •