#Error with the IIF function

[fcmedina78]

I am creating a form that provides criteria to a query for a report. I have
one combo box for the user to select a year, another text box that adds one
for the second year, and another that adds two for the third year. The
calculation for the years works without a problem but when the combobox has
no value I get #error in the text boxes. Here is the expression I am using:

When I enter the expression I enter as:
=IIf(IsNull[QuarterCompYearSelect], ,Val([QuarterCompYearSelect]+1))

Access then converts it to this:
=IIf([QuarterCompYearSelect]="","",Val([QuarterCompYearSelect]+1))

I have tried entering it both ways and get the same error when my year
selection is black. Any Help?

 

[Ken Snell \(MVP\)]

Try this:

=IIf(Len([QuarterCompYearSelect] & "") = 0,
"",Val([QuarterCompYearSelect]+1))

 

[Douglas J. Steele]

IsNull is a function, so the value you're checking needs to be in
parentheses. As well, you must specify a value for the second parameter (the
True parameter:

IIf(IsNull([QuarterCompYearSelect]), Null, Val([QuarterCompYearSelect]) + 1)

Note, too, the correction to the parentheses used with the Val function.

 

[fcmedina78]

Thanks for your help guys. I tried both solutions out of curiosity and this
is what I found:

Entering the following into my crieteria worked:
=IIf(Len([QuarterCompYearSelect] & "") = 0,
"",Val([QuarterCompYearSelect]+1))
Entering the second sugestion gave me #Name error.

Thanks for both of your help!

IsNull is a function, so the value you're checking needs to be in
parentheses. As well, you must specify a value for the second parameter (the
True parameter:

IIf(IsNull([QuarterCompYearSelect]), Null, Val([QuarterCompYearSelect]) + 1)

Note, too, the correction to the parentheses used with the Val function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am creating a form that provides criteria to a query for a report. I have
one combo box for the user to select a year, another text box that adds
one
for the second year, and another that adds two for the third year. The
calculation for the years works without a problem but when the combobox
has
no value I get #error in the text boxes. Here is the expression I am
using:

When I enter the expression I enter as:
=IIf(IsNull[QuarterCompYearSelect], ,Val([QuarterCompYearSelect]+1))

Access then converts it to this:
=IIf([QuarterCompYearSelect]="","",Val([QuarterCompYearSelect]+1))

I have tried entering it both ways and get the same error when my year
selection is black. Any Help?