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.

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")

You can use the IIf or Switch function to accomplish this.

Regards,

Ax

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.

If you are doing nested IIf's, it'd be better - or more readable - to use the Switch statement:
Or, using the previous example:
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

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.

I'll have to look into the switch statements more I've never tryed anything with them, but now you have sparked my interest.