# Thread: if statement in query

1. Registered User
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. Registered User
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. Registered User
Join Date
May 2009
Posts
258
You can use the IIf or Switch function to accomplish this.

Regards,

Ax

4. Registered User
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. Registered User
Join Date
Jul 2009
Posts
4
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. Registered User
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. Registered User
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. Registered User
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
•