IIF STATEMENT HELP

  • Thread starter bluefalcon904 via AccessMonster.com
  • Start date
B

bluefalcon904 via AccessMonster.com

Hi everyone...

Can someone please help me?
I have a table: CHARGE that has several fields:

Account
Currency
Penced
Price
NetMoney

Id like to do a Select Account, Currency, Price, NetMoney from CHARGE

However, would also convert the Price and NetMoney if Currency = GBp and
Penced = Y.

Select Account, Currency, IIf((Currency=GBp) AND (Penced=Y),(Price/100),Price)
), IIf((Currency=GBp) AND (Penced=Y),(NetMoney/100),NetMoney))

Is this right? Please help.

Thanks muchly.
 
T

tina

well, did you try it? if it works, then the syntax is acceptable to the
system - if not, then it isn't.

if it doesn't run, then suggest you lose the internal parentheses around the
comparision statements and the calculation; also, if GBp is a text value,
then surround it with double quotes; and if Penced is a Yes/No field, then
change the Y to True.

hth
 
B

bluefalcon904 via AccessMonster.com

Hi Tina...

Tried it and worked. However, I have another problem:

Im trying to combine the 2 IIFs but it keeps giving me wrong output.

JP_Comm: IIf([JPComm=0="0", Format([JPComm],"Standard"))
JP_Comm: IIF([Currency]="GBp",([JPComm]/100),[JPComm])

If I run separately works fine. However, If I try to combine it, it gives me
the wrong output.

JP_Comm: IIf([Currency]="GBp",IIf([JPComm]=0"0",Format([JPComm],"Standard"),(
[JPComm]/100)),[JPComm])

Its no longer /100 if Currecny is GBp.

What I need is to have an output, if Currency is GBp, then JPComm/100, if not
just JPComm. But if JPComm is 0, then to make sure it displays 0.

Can you please help?

Thank you.
Carl

well, did you try it? if it works, then the syntax is acceptable to the
system - if not, then it isn't.

if it doesn't run, then suggest you lose the internal parentheses around the
comparision statements and the calculation; also, if GBp is a text value,
then surround it with double quotes; and if Penced is a Yes/No field, then
change the Y to True.

hth
Hi everyone...
Click to expand...
[quoted text clipped - 18 lines]
Thanks muchly.
Click to expand...
Click to expand...
 
B

bluefalcon904 via AccessMonster.com

HI Jamie...

Yes but I also need to do multiple IIFs. Seems my IIFs is wrong and Im stuck.
Hope you can help.

Thanks,
Carl
 
B

bluefalcon904 via AccessMonster.com

HI Jamie...

Ill check this out and let you know...

Thank you..

I also need to do multiple IIFs. Seems my IIFs is wrong and Im stuck.
Click to expand...

Try this (test code only, no table involved):

SELECT CCUR(55.00) AS JPComm, 'GBp' AS [Currency],
IIf(
JPComm = CCUR(0.00), '0',
FORMAT(
IIF([Currency] = 'GBp', JPComm * CCUR(0.01), JPComm),
'Standard'
)
);

Often the Jet SWITCH() function - look it up in the *VBA* help - is
preferable to multiple/nested IIF() function calls but I don't think
SWITCH is needed in this case.

An aside about data typing: dividing by an integer will coerce the
result to DOUBLE PRECISION (Double) and floating point arithmetic is
not good for monetary data e.g.

SELECT TYPENAME(CCUR(55.00) / 100)

returns 'Double'.

Even dividing by a currency amount will coerce the result to DOUBLE
PRECISION e.g.

SELECT TYPENAME(CCUR(55.00) / CCUR(100.00))

returns 'Double'.

The trick is to use multiplication e.g.

SELECT TYPENAME(CCUR(55.00) * CCUR(0.01))

returns 'Currency'.

Jamie.

--
Click to expand...
 
Top