Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: IIF forumla in Access Query

    Hi.

    I am trying to get an IIF formula to work in a query and it partially works, but the most important part is not.

    I enter: IIF([Not Accepted]/[Count of Entry]< 100, [Not Accepted]/[Count of Entry], "100%")

    What I want it to do is divide the "Not Accepted" amounts by the "Count of Entry" and if it's more than 100%, to just come back with 100% and if less than 100%, give the actual number.

    It's dividing as I want it to, but it's not coming back with 100% if the results are greater than 100%. I'm not sure what I am missing. Any help would be appreciated.

    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What you wrote is equivalent to:
    Code:
    If [Not Accepted] / [Count of Entry] < 100 Then
        <Returned Value> = [Not Accepted] / [Count of Entry]  ' A number.
    Else
        <Returned Value> = "100%" ' A string.
    End If
    If you want a numeric value to be returned in the second case, you should write:
    Code:
    IIF([Not Accepted]/[Count of Entry]< 100, [Not Accepted]/[Count of Entry], 100)
    Have a nice day!

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Also, I'm not sure what your values are, but 100&#37; numerically is 1, so perhaps that should be your test value.
    Paul

  4. #4
    Join Date
    Apr 2010
    Posts
    2
    Hi. I tried both suggestions (I am looking for a numeric value as a result but nothing greater than 100&#37, but it's still not entering 100 if the result is greater than 100.

    Could I be missing something else?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the whole SQL statement of the query?
    Have a nice day!

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    And some examples of the values in those two fields. A sample db would also help, if you can post one.
    Paul

  7. #7
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Code:
    =IF(Not_Accepted/Count_of_Entry<1,Not_Accepted/Count_of_Entry,1)
    pbaldy's suggestion points out that your formula may need revision. I worked it out in the above Excel formula. Your result will never exceed 1, which can be formatted to "100%".
    Jerry

Posting Permissions

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