Help with IIF in query

[setis]

Hi all,



I have a query (qrySAV) with 2 components A and B. I want to create a new one "C" in which If A>0, C=A, if not, C=B


I have tried: IIF ([qrySAV]![A]>0;[qrySAV]![A];[qrySAV]!)


I am not sure if it is necesary to refer to qrySAV when the elements are in the actual query. When I didn't the error was that the expression A could refer to more than one table.

 

[Gasman]

Use a comma (,) and not semicolon ;

 

[setis]

Use a comma (,) and not semicolon ;

Thanks but it's not that. The regional settings for my office package is configured like that. The union for function's elements is ";" instead of (,). Same thing in excel...

 

[Gasman]

I 've just changed mine to ; and it does not work. humour me and try a , as your syntax looks OK to me.
Have you used ; in the past and have it work?

 

[setis]

I 've just changed mine to ; and it does not work. humour me and try a , as your syntax looks OK to me.
Have you used ; in the past and have it work?

Ok I did and the error is "invalid syntax"


The real one is this:


Savings: IIF([qrySavingsReportTemplate]![SavingsAmount(GBP)]>0, [qrySavingsReportTemplate]![SavingsAmount(GBP)] , [qrySavingsReportTemplate]![EstSaving] )

 

[Gasman]

So what error were you getting with the semicolon version?

 

[setis]

So what error were you getting with the semicolon version?

It asks me for a parameter value for both SavingsAmount(GBP) and EstSaving


see the attached.


Thanks for trying to help

 

[isladogs]

As your query is based on more than one table, you need to specify the table name instead of qrySAV in your expression.

Just for my interest, which country settings use ; instead of , to separate the expression arguments

EDIT I posted this before seeing your last reply. It sounds like your query is built from other queries.
In which case, use the source query for fields 'A' and 'B'

 

[setis]

Just for my interest, which country settings use ; instead of , to separate the expression arguments

Thanks, Demark

 

[setis]

EDIT I posted this before seeing your last reply. It sounds like your query is built from other queries.
In which case, use the source query for fields 'A' and 'B'

This worked! Thank you very much!!