IIf question

[Guest]

I want my IIf statement to put in N/A if the value is either less than 0 or
greater than 0. I want it to put in the VarWComm value only if the value is
equal to 0. Here's my IIf below. Please tell me how to do this.

AmtOfLoss:
IIf([QryClassActRpt.VarWComm]<0,Format([QryClassActRpt.VarWComm],"Currency"),"N/A")

Thank you,

Robert

 

[John Vinson]

I want my IIf statement to put in N/A if the value is either less than 0 or
greater than 0. I want it to put in the VarWComm value only if the value is
equal to 0. Here's my IIf below. Please tell me how to do this.

AmtOfLoss:
IIf([QryClassActRpt.VarWComm]<0,Format([QryClassActRpt.VarWComm],"Currency"),"N/A")

Thank you,

Robert

Put <> 0 instead of <0. What do you want to see if VarWComm is NULL?

John W. Vinson[MVP]

 

[Marshall Barton]

I want my IIf statement to put in N/A if the value is either less than 0 or
greater than 0. I want it to put in the VarWComm value only if the value is
equal to 0. Here's my IIf below. Please tell me how to do this.

AmtOfLoss:
IIf([QryClassActRpt.VarWComm]<0,Format([QryClassActRpt.VarWComm],"Currency"),"N/A")

You have the [ ] around too much and the < should be =

IIf([QryClassActRpt].[VarWComm]=0,Format([QryClassActRpt.VarWComm],"Currency"),"N/A")

 

[RobFMS]

This poses an interesting debate. IIF() does take up a lot of "processing"
time. So be it... for the moment.

One additional item that most people may not realize is that you can create
a public function and call that instead. Again, this is probably just the
same in "processing" time too. The one important advantage is that the
public function allows for ERROR HANDLING to occur. The IIF() does not.. and
additionally, the IIF() must evaluate both the TRUE and FALSE expressions.
If you create a public function, then you can also pass it the necessary
parameter values needed. In this example, I will not illustrate the
parameters.

Tip #33: IIF vs. If...Then...Else in VB, VBA, and VB
..NET
http://www.fmsinc.com/free/NewTips/NET/NETtip33.asp


For example:

Select FirstName, LastName, Nz( AmtOfLoss, GetValue()) as PhoneNumber
From tblPerson

OR

In the example below:
AmtOfLoss: = GetValue()


For which, the function would look something like this:

Public Function GetValue()

On Error Goto Proc_Err

Dim strReturnValue as String

' This If-Then statement is more in psuedo-code since you may need to pass
additional information
If (the field) < 0 Then
strReturnValue = [ format the field the way you like it ]
Else
strReturnValue = "N/A"
End if


Proc_Exit:
GetValue = strReturnValue
Exit Function

Proc_Err:
strReturnValue = "#error#"
Resume Proc_Exit


End Function

I might also recommend creating a global variable that stores the "N/A"
value or the "#error#" value. If you need to modify the strings (because a
user or two is more particular), it is easier to change the value in one
location (in global variable) than to find all the locations in code or in
queries.

HTH


--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

 

[Marshall Barton]

This poses an interesting debate. IIF() does take up a lot of "processing"
time. [Snip important part]
.. and
additionally, the IIF() must evaluate both the TRUE and FALSE expressions.

[snip the point of the post]


Rob, some nitpicking points.

The IIf in a query is not the same as the IIf in VBA.

In a query, the Expression Service IIf is faster than a UDF
because it is built in and does not need to transition to
VBA processing.

Furthermore, the Expression Service IIf only evaluates the
True or the False parts, not both. This can be tested by
using a simple VBA procedure:

Sub TestIIf()
Dim x
x=IIf(True,MsgBox("VBA:True"),MsgBox("VBA: False"))
x=IIf(False,MsgBox("VBA:True"),MsgBox("VBA:False"))
x=Eval("IIf(True,MsgBox(""ES:True""),MsgBox(""ES:False""))")
x=Eval("IIf(False,MsgBox(""ES:True""),MsgBox(""ES:False""))")
End Sub

Actually, the Expression Service IIf does not even require
the False expression. It will supply a default Null when
you omit the False expression.
Debug.Print Eval("IIf(False,MsgBox(""ES:True"")")

 

[RobFMS]

Quite interesting Marshall.
I'll look over your information in more detail.

Thanks for the info.

--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

This poses an interesting debate. IIF() does take up a lot of "processing"
time. [Snip important part]
.. and
additionally, the IIF() must evaluate both the TRUE and FALSE expressions.

[snip the point of the post]


Rob, some nitpicking points.

The IIf in a query is not the same as the IIf in VBA.

In a query, the Expression Service IIf is faster than a UDF
because it is built in and does not need to transition to
VBA processing.

Furthermore, the Expression Service IIf only evaluates the
True or the False parts, not both. This can be tested by
using a simple VBA procedure:

Sub TestIIf()
Dim x
x=IIf(True,MsgBox("VBA:True"),MsgBox("VBA: False"))
x=IIf(False,MsgBox("VBA:True"),MsgBox("VBA:False"))
x=Eval("IIf(True,MsgBox(""ES:True""),MsgBox(""ES:False""))")
x=Eval("IIf(False,MsgBox(""ES:True""),MsgBox(""ES:False""))")
End Sub

Actually, the Expression Service IIf does not even require
the False expression. It will supply a default Null when
you omit the False expression.
Debug.Print Eval("IIf(False,MsgBox(""ES:True"")")

 

[Randy Harris]

Furthermore, the Expression Service IIf only evaluates the
True or the False parts, not both. This can be tested by
using a simple VBA procedure:

Sub TestIIf()
Dim x
x=IIf(True,MsgBox("VBA:True"),MsgBox("VBA: False"))
x=IIf(False,MsgBox("VBA:True"),MsgBox("VBA:False"))
x=Eval("IIf(True,MsgBox(""ES:True""),MsgBox(""ES:False""))")
x=Eval("IIf(False,MsgBox(""ES:True""),MsgBox(""ES:False""))")
End Sub

Actually, the Expression Service IIf does not even require
the False expression. It will supply a default Null when
you omit the False expression.
Debug.Print Eval("IIf(False,MsgBox(""ES:True"")")

Subtle, but important distinctions. I realize I'm being a bit OT, but when
you guys post this sort of stuff, everyone here benefits.

Thanks Marsh,
Randy Harris

 

[Guest]

Would that explain why when I use an Access Expression like:

WLKLST_DSCR:
IIf(IsNull([Q_ACC_CUST_WLKLST_GRP].[WLKLST_CD]),[T_ACC_SLS_WLKLST].[WLKLST_DSCR],"Unknown")

I get:
WLKLST_DSCR CountOfSYN_ID
Unknown 121

But when I reverse the order to:

WLKLST_DSCR:
IIf(IsNull([Q_ACC_CUST_WLKLST_GRP].[WLKLST_CD]),"Unknown",[T_ACC_SLS_WLKLST].[WLKLST_DSCR])

I get:
WLKLST_DSCR CountOfSYN_ID
Bad Credit 20
Lost Other 7
Low Volume 29
Stay w/ ABC, Leave United, Join PPN 30
Stay w/ wholesaler, stay w/ United 19
Stay with United / Move to CAH 11
Walk Other 5



I am trying to capture the 121 null values in my second example.. but they
seem not to process? Is there another method I can use with expression
builder?

 

[Randy Harris]

Would that explain why when I use an Access Expression like:

WLKLST_DSCR:
IIf(IsNull([Q_ACC_CUST_WLKLST_GRP].[WLKLST_CD]),[T_ACC_SLS_WLKLST].[WLKLST_D
SCR],"Unknown")

I get:
WLKLST_DSCR CountOfSYN_ID
Unknown 121

But when I reverse the order to:

WLKLST_DSCR:
IIf(IsNull([Q_ACC_CUST_WLKLST_GRP].[WLKLST_CD]),"Unknown",[T_ACC_SLS_WLKLST]
..[WLKLST_DSCR])

I get:
WLKLST_DSCR CountOfSYN_ID
Bad Credit 20
Lost Other 7
Low Volume 29
Stay w/ ABC, Leave United, Join PPN 30
Stay w/ wholesaler, stay w/ United 19
Stay with United / Move to CAH 11
Walk Other 5



I am trying to capture the 121 null values in my second example.. but they
seem not to process? Is there another method I can use with expression
builder?

My guess, from what you posted, is that you have that in a Group By
statement in your aggregate query. The Group By will show each unique
value. Since the first form has only one - "Unknown" only that line shows.
This is because WLKLST_CD is never null.

It's not clear to me, however, how you would like it to appear that is
different from what you are getting in the second example. What do you mean
by "they seem not to process"? The way you have it written, the Iif really
doesn't do anything at all.

So, if my assumption is correct, the answer to your question is no, it's the
Group By that causes the difference in output.