access query formula

[simpleman]

Hi I am a self employed man with a limited knowledge of access but I like using it and enjoy what is to me a challenge.

When our wonderfull leader changed the VAT rate to 15% I altered my VAT formula to IIf([SALES ID]<16988,[SUB TOTAL]*[VAT RATE]) & IIf([SALES ID]>16987,[SUB TOTAL]*[NEW RATE]) worked brilliantly!!

Now soon comes Jan and we are returning to 17.5%, in my infinate wisdom I thought adding & IIf([SALES ID]>19450,[SUB TOTAL]*[VAT RATE]) would do the trick and take me back to the old rate but it does not work, been tearing my hair out since but cannot make anything do the trick.



Any help would be much appreciated

[RuralGuy]

That is not a valid IIF statement. Care to post it again?

[simpleman]

Hi and many thanks for taking the trouble to reply, IIf([SALES ID]<16988,[SUB TOTAL]*[VAT RATE]) & IIf([SALES ID]>16987,[SUB TOTAL]*[NEW RATE]) did work, been using it for over a year, on receiving your reply I had a fiddle and realised that>

IIf([SALES ID]<16988,[SUB TOTAL]*0.175) & IIf([SALES ID]>16987,[SUB TOTAL]*0.15) worked just as well and meant I could delete two fields from my database [vat Rate] and [new rate]

I figured out in the end that I was asking it to perfom the same sum, on top by adding & IIf([SALES ID]>18450,[SUB TOTAL]*0.175)

I have ended up after not sleeping all night (why is this stuff so addictive)
with VAT: IIf([SALES ID] Between 0 And 16987,[SUB TOTAL]*0.175) & IIf([SALES ID] Between 16988 And 18450,[SUB TOTAL]*0.15) & IIf([SALES ID]>18450,[SUB TOTAL]*0.175) this seems to works fine.

Anyway thanks again for taking the trouble to reply it is appreciated and you made me go back and look harder

[genesis]

can you state what is that sales id there?

also your IIF statement is erroneous!

syntax is iif(criteria,valueiftrue,iif(criteria,valueiftrue, if(criteria,valueiftrue,valueiffalse)))

there is no use of "&" there.

[RuralGuy]

I also have a problem with your use of the ampersand "&".

[simpleman]

Guys got to tell that it works,
VAT: IIf([SALES ID] Between 0 And 16987,[SUB TOTAL]*0.175) & IIf([SALES ID] Between 16988 And 18450,[SUB TOTAL]*0.15) & IIf([SALES ID]>18450,[SUB TOTAL]*0.175)
may not be right but it honestly does work
I did state that I am a self taught amateur .

Sales ID is actually the invoice no and the idea is that up to invoice 16987
the tax is calculated 17.5% from 16988 to and including 18450 tax is calculated at 15% and from January next year used 18450 just as a figure for the test, it goes back to 17.5%

Having said all that the formula below based on what genesis suggested also works and is no doubt the correct way to do it, I have now changed it in my database and many thanks for the lesson.

VAT: IIf([SALES ID] Between 0 And 16987,[SUB TOTAL]*0.175,IIf([SALES ID] Between 16988 And 18450,[SUB TOTAL]*0.15,IIf([SALES ID]>18450,[SUB TOTAL]*0.175)))

[RuralGuy]

We believed you, it was just that the syntax was strange. Evently the Access parser will accept either a comma "," or the ampersand "&" there. I probably would have created a public function in a standard module for your function and used a Case structure. Easier to understand and maintain in my opinion.