Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Unanswered: Nested IIf() statements in query

    Hi. I was wondering if anyone knew of a way to have a query that runs a specific nested IIf statement based on a certain company. The way I have it set up now is in the main query, and it works just fine, but it will only work on 1 company. I then made a table that had companies and the nested IIf statement that went with each one. When I tied the company field together and dropped the IIf field into the query, it showed the IIf function written out instead of the calculated number. Any suggestions would be appreciated.

    Thanks.

    David
    Last edited by decad1; 04-25-03 at 12:35.

  2. #2
    Join Date
    Apr 2003
    Posts
    31

    sample query

    what are you trying to return?

    If cboBox1 = "CompanyA" Then
    Select Case ObjectType1
    Case "is equal to"
    SQL1 = "SELECT tblQuestions.* FROM tblQuestions WHERE (tblQuestions." & Me!cboProperty & "=" & Quotes$ & Me!txtValue & Quotes$

    Case Else
    SQL = ""
    End Select

    ElseIf cboBox1 = "CompanyB" Then

    EndIf

  3. #3
    Join Date
    Apr 2003
    Posts
    31

    sample query

    what are you trying to return? if you give me a little more information, i'll be able to define the query better.

    ------------------------sample------------------------------
    Dim SQL$
    Dim db As Database, Qd As QueryDef, Quotes$

    Set db = CurrentDb()
    Set Qd = db.QueryDefs("qryExistingQuery")
    Set ObjectType1 = cboCompanyName

    If cboCompanyName = "CompanyA" Then
    Select Case ObjectType1
    Case "is equal to"
    SQL$ = " your Query Statement"

    Case Else
    SQ$ = ""

    End Select

    ElseIf cboCompanyName = "CompanyB" Then

    EndIf
    --------------------------------------------------------------------------------

  4. #4
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    I am working on a way to compare how hogs would be paid if they were processed in different plants. The first thing I am talking about that is different for each company is the premiums/discounts being paid for percent lean (The leaness of the hog). For Example, company A's payouts look like this:

    Lean Premium Factor: IIf([PercentLean]>0.58,6,IIf([PercentLean]>0.56,5,IIf([PercentLean]>0.54,4,IIf([PercentLean]=0.54,3.25,IIf([PercentLean]=0.53,2.75,IIf([PercentLean]=0.52,1.75,IIf([PercentLean]=0.51,0.75,IIf([PercentLean]=0.49,-1,IIf([PercentLean]=0.48,-2,IIf([PercentLean]=0.47,-3,IIf([PercentLean]=0.46,-4,IIf([PercentLean]=0.45,-6,IIf([PercentLean]=0.44,-8,IIf([PercentLean]<0.44,-10,0))))))))))))))

    I want the query to run the right Lean Premium Factor Calculation based on which company was being compared. Right now, I have to put the manual calculation in and run it individually. I hope this is specific enough. I can get more specific if you want, but I'm not quite sure how best to explain it.

    Thanks.

  5. #5
    Join Date
    Mar 2003
    Posts
    46
    Hi there,

    Create a separate table with the 'leanness' bands and relate it to the company table ie. have a table with 4 columns 'CoID', UpperLeanness, Payout, LowerLeannes with values CompanyA,1.00,6,0.58; CompanyA,0.58,5,0.56; CompanyA,0.56,4,0.54; etc. until you've entered all 'bands' for Company A.

    Relate the table to the company table and for whatever field defines what the specific 'leanness' is set the following criteria: <=[UpperLeanness] And >[LowerLeanness]

    This should enable you to easily expand your list of companies as well as amend the 'bands' that define the payouts.

    CCC

  6. #6
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    Thanks! I had never even thought of that, but that is very simple and keeps you from having to use comlex IIf statements. That is sweet. I can use that on other applications.

    Thanks again.

    David

Posting Permissions

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