Solved Unexpected result from iif()

[smaction]

I wrote a piece of code to test an issue with an iif() statement. I am getting an unexpected result. The code below displays 3 msg boxes (all with the expected data) instead of 2. What have I forgotten/ missed in this code?

Private Sub Command35_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim dtFieldValue As Date
Dim varHold As Variant

stDocName = "Contracts Tabbed Read Only2"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord acDataForm, "Contracts Tabbed Read Only2", acGoTo, 10
dtFieldValue = Forms![Contracts Tabbed Read Only2].[Current Term Exp]
varHold = IIf(dtFieldValue < Date, MsgBox(dtFieldValue), MsgBox("false"))
MsgBox varHold

End Sub

 

[Isaac]

How does this work?

Private Sub Command35_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim dtFieldValue As Date
Dim varHold As Variant

stDocName = "Contracts Tabbed Read Only2"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord acDataForm, "Contracts Tabbed Read Only2", acGoTo, 10
dtFieldValue = Forms![Contracts Tabbed Read Only2].[Current Term Exp]
varHold = IIf(dtFieldValue < Date, dtFieldValue, "False")
MsgBox varHold

End Sub

 

[plog]

You set varHold to a MsgBox:

varHold = IIf(dtFieldValue < Date, MsgBox(dtFieldValue), MsgBox("false"))
MsgBox varHold

So, in setting a value to varHold a message box appears (#1). Then in the last line you message box (#2) a message box (#3).

 

[smaction]

Thank you both. Now I can get on with testing the underlying problem I am using this code snippet to examine.

 

[cheekybuddha]

IIRC, IIf() evaluates both the True and False parts of the expression irrespective of the result of the test.

This can be problematic in the case where you use the test to avoid an error, e.g.:

Dim x As Double, y As Integer

y = 0
x = IIf(y > 0, 1 / y, 0)

The above gives error 11, Division by zero

 

[cheekybuddha]

However, this only true in VBA. When used in an expression in a query it's OK;

 

[Isaac]

Thank you both. Now I can get on with testing the underlying problem I am using this code snippet to examine.

Glad you got it working and on to the next!