IIF STATEMENT HELP

[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.

 

[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

 

[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...

[quoted text clipped - 18 lines]

Thanks muchly.

 

[onedaywhen]

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.

Is your problem that

SELECT FORMAT(0, 'Standard')

returns '0.00'?

In which case, change the format text e.g. to

SELECT FORMAT(CCUR(0.00), '#,##0.00;-#,##0.00;0;{{NONE}}')

The semicolon-delimited format string is '<postive values>;<negative
values>;<zero values>;<NULL values>'.

Jamie.

--

 

[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

 

[onedaywhen]

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

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.

--

 

[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.

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.

--