var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: IIF forumla in Access Query
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.
What you wrote is equivalent to:
If you want a numeric value to be returned in the second case, you should write:
If [Not Accepted] / [Count of Entry] < 100 Then
<Returned Value> = [Not Accepted] / [Count of Entry] ' A number.
<Returned Value> = "100%" ' A string.
IIF([Not Accepted]/[Count of Entry]< 100, [Not Accepted]/[Count of Entry], 100)
Have a nice day!
Also, I'm not sure what your values are, but 100% numerically is 1, so perhaps that should be your test value.
Hi. I tried both suggestions (I am looking for a numeric value as a result but nothing greater than 100%, but it's still not entering 100 if the result is greater than 100.
Could I be missing something else?
What's the whole SQL statement of the query?
Have a nice day!
And some examples of the values in those two fields. A sample db would also help, if you can post one.
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%".