If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Error with the IIF function
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? |
#2
|
|||
|
|||
#Error with the IIF function
Try this:
=IIf(Len([QuarterCompYearSelect] & "") = 0, "",Val([QuarterCompYearSelect]+1)) -- Ken Snell MS ACCESS MVP "fcmedina78" wrote in message ... 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? |
#3
|
|||
|
|||
#Error with the IIF function
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 http://I.Am/DougSteele (no private e-mails, please) "fcmedina78" wrote in message ... 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? |
#4
|
|||
|
|||
#Error with the IIF function
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! "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no private e-mails, please) "fcmedina78" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|