Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    10

    Unanswered: if statement in query

    Is there a way to get an if then or a case statement to work inside a query?

    I need to be able to give percent under over a set range of numbers and as of yet have been unable to get it to work inside of a query, and splitting the query then bringing it back together does no good so far. Open to whatever suggestions anyone has.

  2. #2
    Join Date
    Mar 2009
    Posts
    37
    How did you put the if statement in? Can you copy the code into your posting?

    try something like this iif(percentage>0, "what you want it to do","otherwise do this")

  3. #3
    Join Date
    May 2009
    Posts
    258
    You can use the IIf or Switch function to accomplish this.

    Regards,

    Ax

  4. #4
    Join Date
    Jul 2009
    Posts
    10
    So i tried the following
    =iif([result]>=([forms]![range]![range mid]-10 and [result]<=([forms]![range]![range mid]+10,100,+iif(([result]<([forms]![range]![range mid]-10),(([result]/([forms]![range]![rangemid]-10))*100),+iif([result]<([forms]![range]![range mid]+10),((([forms]![range]![range mid]+10)-([result]-([forms]![range]![range mid]+10))/([forms]![range]![range mid]+10))*100),0))

    Which is now giving me "the expression you entered is missing closing parenthesis, bracket (]), or vertical bar(|)" error

    if i tack a ) on the end I get " the expression you entered has a function containing the wrong number of arguments."

    range mid = 130
    range it +- 10 from range mid

    result comes from a table of data previously entered

    so at this point it is not letting the query run

    ????????????????????

  5. #5
    Join Date
    Jul 2009
    Posts
    4
    Quote Originally Posted by drr6670
    So i tried the following
    =iif([result]>=([forms]![range]![range mid]-10 and [result]<=([forms]![range]![range mid]+10,100,+iif(([result]<([forms]![range]![range mid]-10),(([result]/([forms]![range]![rangemid]-10))*100),+iif([result]<([forms]![range]![range mid]+10),((([forms]![range]![range mid]+10)-([result]-([forms]![range]![range mid]+10))/([forms]![range]![range mid]+10))*100),0))

    Which is now giving me "the expression you entered is missing closing parenthesis, bracket (]), or vertical bar(|)" error

    if i tack a ) on the end I get " the expression you entered has a function containing the wrong number of arguments."

    range mid = 130
    range it +- 10 from range mid

    result comes from a table of data previously entered

    so at this point it is not letting the query run

    ????????????????????
    With long expressions it helps if you break them up first to line up the ()s. Without going too far in I can see that you seem to be putting the ()s in the wrong place.

    =iif([result]>=
    ([forms]![range]![range mid]-10 and [result]<=
    ([forms]![range]![range mid]+10,100,+iif
    ( ...

    should be

    =iif([result]>=[forms]![range]![range mid]-10 and
    [result]<=[forms]![range]![range mid]+10,100,
    iif(...


    It would probably make your code a lot more readable if you created a couple of variables like this.

    Set mdRngP10 = [forms]![range]![range mid]+10
    Set mdRngM10 = [forms]![range]![range mid]-10

    And you get something like this.

    =iif([result]>=mdRngM10 and [result]<=mdRngP10,100,
    iif([result]<mdRngM10,[result]/mdRngM10*100,
    iif([result]<mdRngP10,mdRngP10-([result]-mdRngP10)/mdRngP10*100,0)
    )
    )


    Hope that helps.


    Edit: Oh and don't forget to add the dim statement for mdRngP10 and mdRngM10 if you add them as variables.
    Last edited by sbjnyc; 07-02-09 at 12:48.

  6. #6
    Join Date
    May 2009
    Posts
    258
    If you are doing nested IIf's, it'd be better - or more readable - to use the Switch statement:
    Code:
    Switch([result] >= [Forms]![range]![range mid] - 10 And [result] <= [Forms]![range]![range mid] + 10, 100, _
        [result] < [Forms]![range]![range mid] - 10, [result] / ([Forms]![range]![range mid] - 10) * 100, _
        [result] < [Forms]![range]![range mid] + 10, ([Forms]![range]![range mid] + 10 - ([result] - ([Forms]![range]![range mid] + 10)) / ([Forms]![range]![range mid] + 10)) * 100)
    Or, using the previous example:
    Code:
    Dim mdRngP10, mdRngM10
    mdRngP10 = [forms]![range]![range mid]+10
    mdRngM10 = [forms]![range]![range mid]-10
    Debug.Print Switch([result] >= mdRngM10 And [result] <= mdRngP10, 100, _
        [result] < mdRngM10, [result] / (mdRngM10) * 100, _
        [result] < mdRngP10, (mdRngP10 - ([result] - (mdRngP10)) / (mdRngP10)) * 100)
    There is an error in your logic that will result in the third condition never being met, since that condition will always be covered in the first condition.

    Regards,

    Ax
    Last edited by Ax238; 07-02-09 at 15:09.

  7. #7
    Join Date
    Jul 2009
    Posts
    10
    It would probably make your code a lot more readable if you created a couple of variables like this.

    Set mdRngP10 = [forms]![range]![range mid]+10
    Set mdRngM10 = [forms]![range]![range mid]-10

    And you get something like this.

    =iif([result]>=mdRngM10 and [result]<=mdRngP10,100,
    iif([result]<mdRngM10,[result]/mdRngM10*100,
    iif([result]<mdRngP10,mdRngP10-([result]-mdRngP10)/mdRngP10*100,0)
    )
    would have been al fine and dandy, but I couldn't get it to take the variables inside of the query.
    ended up adding 2 hidden boxes to the form it pulls info from for high and low end of range and corrected my > typo

    this is what I ended up with

    IIf([result]<=[forms]![range]![hg] And [result]>=[forms]![range]![lw],100,IIf([result]<[forms]![range]![lw],[result]/[forms]![range]![lw]*100,IIf([result]>[forms]![range]![hg],[forms]![range]![hg]-([result]-[forms]![range]![hg])/[forms]![range]![hg]*100,0)))



    Thank you for the help on this one.

  8. #8
    Join Date
    Jul 2009
    Posts
    10
    to use the Switch statement
    I'll have to look into the switch statements more I've never tryed anything with them, but now you have sparked my interest.

Posting Permissions

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