If Else in Query

[wdverner]

Hi guys,
I have this field set up in my query:

SP STG: IIf([Cur]="EUR",[Selling Price]*0.6998)

Basically if the Customer currency is EUR then convert to GBP (Sterling)

However, some customers currency is GBP and so I want to add this in so that no calculation is performed i.e. Only currencies in EURO are converted to sterling.

SO I need some sort of statement like If Cur= EUR *0.6998, If Cur= GBP do nothing..

can I do this within one field?

Thanks for your time guys

 

[Hinfer]

Try this:

switch(
[Cur]="EUR",[Selling Price]*0.6998,
true,[Selling Price]
)

a switch is like a case statement. This does the follwoing

if euro then calculate
else (all other currencies) return selling price

 

[PCLewis]

Actually, your problem was in misunderstanding how IIF works I think.

In your expression, You will get [Selling Price]*0.6998 when [CUR] is "EUR" however in any other case, you will recieve a value of FALSE which is 0 if it's a numeric field.

You should try
IIF([Cur]="EUR",[Selling Price]*0.6998,[Selling Price])

As for using switch, which is a good function too, Hinfer's example is spot on. The advantage of Switch is when you have more than two options.