using the IIF

  • Thread starter Thread starter ACSer
  • Start date Start date
A

ACSer

i am having trouble with getting the statement to work like i need:

IIf([REFUND_LETTERS_ALL_COUNT].Counter=1,"First Request","Second Request ")
AS Expr1

What I need to do is nest this statement so that the result will show
Counter = 0, blank
Counter= 1, “First Requestâ€
Counter = 2, “Second Requestâ€
Etc….

As of now, it returns "Second Request for all records.
 
i am having trouble with getting the statement to work like i need:

IIf([REFUND_LETTERS_ALL_COUNT].Counter=1,"First Request","Second Request ")
AS Expr1

What I need to do is nest this statement so that the result will show
Counter = 0, blank
Counter= 1, ¡§First Request¡¨
Counter = 2, ¡§Second Request¡¨
Etc¡K.

As of now, it returns "Second Request for all records.
Click to expand...

Here is the syntax to nest iif statements.
IIf(Criteria=A,TrueValueA,IIf(Criteria=B,TrueValueB,FalseValue))

so using your values:

IIf([REFUND_LETTERS_ALL_COUNT].Counter=1,"First Request",
IIf([REFUND_LETTERS_ALL_COUNT].Counter=2,"Second Request,""))
AS Expr1

However I don't like the sound of that Etc... you placed at the end.
If you have a lot of choices there are better functions to use.
Look up Choose(), and Select Case in VBA Help.
 
i had attempted to use the case statement originally and it bombed...
i used this and with the syntax error corrected, gave me the result needed,
however i am still showing duplicate records for these requests, got any
ideas to stop the duplication?
if the letter

fredg said:
i am having trouble with getting the statement to work like i need:

IIf([REFUND_LETTERS_ALL_COUNT].Counter=1,"First Request","Second Request ")
AS Expr1

What I need to do is nest this statement so that the result will show
Counter = 0, blank
Counter= 1, “First Requestâ€
Counter = 2, “Second Requestâ€
Etc….

As of now, it returns "Second Request for all records.
Click to expand...

Here is the syntax to nest iif statements.
IIf(Criteria=A,TrueValueA,IIf(Criteria=B,TrueValueB,FalseValue))

so using your values:

IIf([REFUND_LETTERS_ALL_COUNT].Counter=1,"First Request",
IIf([REFUND_LETTERS_ALL_COUNT].Counter=2,"Second Request,""))
AS Expr1

However I don't like the sound of that Etc... you placed at the end.
If you have a lot of choices there are better functions to use.
Look up Choose(), and Select Case in VBA Help.
Click to expand...
 
i had attempted to use the case statement originally and it bombed...
i used this and with the syntax error corrected, gave me the result needed,
however i am still showing duplicate records for these requests, got any
ideas to stop the duplication?
Click to expand...

Sounds like a different problem with the query; please open the query in SQL
view and post it here. An example of what kind of "duplicate" you're seeing
might help too.
 
Back
Top