IIF STATEMENT HELP

  • Thread starter Thread starter bluefalcon904 via AccessMonster.com
  • Start date 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.
 
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 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...
 
bluefalcon904 said:
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.
Click to expand...

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.

--
 
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
 
bluefalcon904 said:
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.

--
 
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...
 
Back
Top