IIF expression problem - Pls help

[gumbira]

Hi,

I'm trying to build a report where one of the fields is populated by data from a table and I'm trying to use the IIF statement to filter the data coming in.

=IIf(([dbo_vwSpotPositionMoveByBranchDealerCurrencyPortfolio]![Portfolio])="MTDPAL"," ",[dbo_vwSpotPositionMoveByBranchDealerCurrencyPortfolio]![Portfolio])

Essentially I'm trying to create the report such that if the data is MTDPAL then it returns a blank but if not then it goes ahead with the values in the field Portfolio.
When I try this though, all I get is #Error on my report.
Can someone please help, thanks!

 

[Taff]

Hi gumbira,

Should you have a space in the word Portfo lio?

=IIf(([dbo_vwSpotPositionMoveByBranchDealerCurrencyPortfo lio]![Portfolio])="MTDPAL"," ",[dbo_vwSpotPositionMoveByBranchDealerCurrencyPortfo lio]![Portfolio])

Ant.

 

[gumbira]

Hi Ant,

Sorry that was just a mistake when copying the line out of Access, it's not in the original expression.
Thanks for replying anyway, do you see anything else wrong with the syntax?

 

[Taff]

Not really,

Are you using the IIF statement in a query?

Ant.

 

[gumbira]

No
Just applying it to a field box in a report.

 

[Taff]

Create a query with all the fields you require and then try pasting the following as the field in your query:-

Expr1: =IIf(([dbo_vwSpotPositionMoveByBranchDealerCurrencyPortfo lio]![Portfolio])="MTDPAL"," ",[dbo_vwSpotPositionMoveByBranchDealerCurrencyPortfo lio]![Portfolio])

Then just include Expr1 in your report. You can change the name of Expr1 if you want to aswell.

Hope this helps.

Ant.

 

[Rich]

=IIf([Portfolio]="MTDPAL"," ",[Portfolio])
or
=IIf([Portfolio]="MTDPAL",Null,[Portfolio])