Help with IIf?

[seat172]

Hi guys, I have a query that is trying to calculate a tax amount based on invoice value, but is variable depending on a customer tax code.

So if a customer has tax code A,( this info is stored in the customer table) then tax is 17.5%, if tax code is X then it is zero but i still need to show both record in the output.



Tried this, forget the calculation just simplified it for reading
:IIF([taxcode]=A,([value]+17.5%),value)

Value being the invoice total from my invoice table. So if the tax code is =A i want the calculation done if not i want just the value from the invoice table.

It don't work! Any tips very welcome.

Thanks in advance.

 

[ColinEssex]

IIf([Taxcode]="A",(([Value]*17.5)/100)+[value],[Value])

Col

 

[KenHigg]

IIf([Taxcode]="A",[Value]*1.175),[Value])

???
kh

 

[ColinEssex]

Thanks Ken - although mine worked, it did look a bit odd

Col

 

[KenHigg]

Cool - I usually don't see those things...

Actually, I was wondering if he stores 'A' or 17.5 in the customer table and how historical data integrity is maintained if this fld changes..

kh

 

[seat172]

Thanks

Thanks for the reply all, still having problems, the tables are on an old AS400 system so i have no control over them they are just linked to my database. What you have come up with is what i have done but it still don't work.

Please see actual pasted below,
IIf([HDQBPC51BF_RCM]![CTAX]="A",HDQBPC51MF_MRA20]![MRNETT]*1.175,[HDQBPC51MF_MRA20]![MRNETT])

Which, excuse the library names the HDQ bit, the tables are the RCM and MRA20 the fields are the CTAX and MRNETT.

When i run it i get an enter parameter HDQBPC51BF_RCM!TAX, i thought i had done that in the above statement, the "A" bit.

Any more thoughts?

Thanks

 

[KenHigg]

This may not help, but you missed a bracket:

IIf([HDQBPC51BF_RCM]![CTAX]="A",[HDQBPC51MF_MRA20]![MRNETT]*1.175,[HDQBPC51MF_MRA20]![MRNETT])


kh

 

[seat172]

More Thanks

Sorry, just my keyboard dyslexia i do have the brackets in the right place in my statement

 

[KenHigg]

Try to simplify things a bit, for testing. Maybe:

IIf([HDQBPC51BF_RCM]![CTAX]="A","yes","no")

???
kh

btw - You could rename your attached tables to something more meaningful if you wanted to.

 

[seat172]

Thanks For Your Help

Thanks for your help, done it! Created a local table of the tax codes and it worked fine, try to pull it direct from the AS400 table it don't work??? Although it copes ok with the rest of the AS400 info and calcs no problem.

Many Thanks