iif function in a report textbox returning #error...why?

[J_Orrell]

Hello all, can I run something really simple past you? I can't for the life of me figure out why it's not working.

The source query for my report has a field called Sort Code.
If I put a text box on the report and type its Control Source as [Sort Code], sure enough the report displays the Sort Code field. However I want to build-in an 'iif' statement in the report (not the query) that returns a blank field if the sort code is a certain value. So just from the perspective of trying to get the logic to work, why does this, typed into the Control Source:

=IIF(1=1,[Sort Code],"")

...return #Error instead of the Sort Code field?

Running Access 2003. Thanks

 

[JHB]

Try by replacing the "," with a ";".

=IIF(1=1[B][COLOR=Red];[/COLOR][/B][Sort Code][B][COLOR=Red];[/COLOR][/B]"")

 

[J_Orrell]

Hi that doesn't work, the correct syntax is a comma, it just changes it back to a comma

 

[J_Orrell]

I have solved this problem. The '#Error' problem was caused because the name of the text box on the report was also Sort Code, so referring to [Sort Code] in the IIF function caused some kind of ambiguity error. Changing the name of the text box to 'txtSortCode' made the IIF function return the correct value.

 

[CJ_London]

what have you called your text box? If it is called 'Sort Code' then it is trying to call itself.

PS spaces in names is a bad idea - use the caption property in table design if necessary for populating label captions

Ah - you beat me to it!

 

[Galaxiom]

Hi that doesn't work, the correct syntax is a comma, it just changes it back to a comma

The syntax is regional. The function argument separator is a semicolon in regions where the decimal separator is a comma (much of mainland Europe). JHB is from Denmark.

 

[J_Orrell]

The syntax is regional. The function argument separator is a semicolon in regions where the decimal separator is a comma (much of mainland Europe). JHB is from Denmark.

Fair enough, thanks