Hello,
I am trying to write this "if" statement for a query. I keep getting syntax errors and was wondering if anyone can take a look and see anything. Also is this the best way to do this-should it be a function in a if else statement?

IF( [Z] < [Y] -0.5), A, IF (AND(( [Z] > [Y] +.5),( [Z] < [Y] + 1.5)), C, IF (AND(( [Z] < [Y] +.5),( [Z] > [Y] -0.5)),B,D))))

Thanks is advance for your help.

You need to use the IIF statement, not the IF statement.


IIf([Z] < ([Y] - 0.5), A, IIf(([Z] > ([Y] + 0.5)) And ([Z] < ([Y] + 1.5)), _
C, IIf(([Z] < ([Y] + 0.5)) Or ([Z] > ([Y] - 0.5)), B, D)))


.

Thank you, I tried the below code and get the error that I have the wrong number of arguments.

IIF( [Z] < [Y] -0.5), "A", IIF (AND(( [Z] > [Y] +.5),( [Z] < [Y] + 1.5)),"C", IIF (AND(( [Z] < [Y] +.5),( [Z] > [Y] -0.5)),"B","D"))))

Try This

IIF( [Z] < [Y] -0.5, "A", IIF ( [Z] > [Y] +.5 and [Z] < [Y] + 1.5, "C", IIF ([Z] < [Y] +.5 and [Z] > [Y] -0.5,"B","D")))

Thanks-but still getting same error

I had added the procedure to my previous post (bout 2 minutes after khawar :D).
See if that works. If not then post the entire query.

.

Download the attached sample I used the same code in the query that I have posted and its working fine

Thank you!!! Worked like a charm

Hi -

You may find the Switch() function easier to deal with in that you don't get wrapped around the axle re parentheses, improper number of arguments.


x = switch([Z]<([Y]-0.5), "A", [Z]>([Y]+0.5) and [Z]<([Y]+1.5), "C", [Z]<([Y]+0.5) and ([Z]>([Y]-0.5),"B", True, "D")

Bob