VAT Lookup Query

[groovybunny]

Hi Guys, I hope someone can help me with this query!

I have a badly written database to fix, I have invoice generating queries that contain the code

VAT: IIf([VAT Applicable]=True,[Amount]*0.175,0)

If i simply change all of the queries to 15% the historical data will be affected, so I need to add a date parameter and a VAT value table.


The new vat table has 3 columns. Start date, end date and VAT rate

Basically i think i need to say something like...

VAT: iif [VAT applicable]=True, SELECT Vat.Rate
FROM Vat
WHERE GetDate() >= Vat.StartDate AND GetDate < Vat.EndDate;

This isnt working and I think I am missing something really basic! can anyone help me to fix it please

Thank You!

 

[jackd]

Hi Guys, I hope someone can help me with this query!

I have a badly written database to fix, I have invoice generating queries that contain the code

VAT: IIf([VAT Applicable]=True,[Amount]*0.175,0)

If i simply change all of the queries to 15% the historical data will be affected, so I need to add a date parameter and a VAT value table.


The new vat table has 3 columns. Start date, end date and VAT rate

Basically i think i need to say something like...

VAT: iif [VAT applicable]=True, SELECT Vat.Rate
FROM Vat
WHERE GetDate() >= Vat.StartDate AND GetDate < Vat.EndDate;

This isnt working and I think I am missing something really basic! can anyone help me to fix it please

Thank You!

I think you're on the right sort of track in that you only want to affect selected records.
So, I think the question is something like this -- and you have to determine which records should be affected --

IiF ( [VAT Applicable]= TRUE And _
DateofTransactionRequiresVatCalculation = TRUE , Amount *VatRate, 0)

where DateofTransactionRequiresVatCalculation is a determined by
The Date of the Transaction is within the Range of Dates for this VatRate

Historical data could(will) be an issue if the historic VAT rate has changed from time to time.
A key point to your calculation will be the >= and <= for Dates.

You won't be able to use the Select statement as you have shown.

Process will be along this line:
Get the Date of the transaction (and this is where your intimate knowledge of the application comes in --- is it the date the transaction occurred or the date the transaction was processed etc..)
Use that date to look into the VATrates Table, Get the VatRate that applies, then use it in your iif statement

VAT: IiF ( [VAT Applicable]= TRUE , Amount *VatRate, 0)

 

[kourtney]

Can a trader charge me vat retrospectively because he forgot to add it to my final invoice?Had a shower fitted, have paid final invoice total. Trader forgot to add VAT now wants me to pay it retrospectively - do i have to pay it or was that final invoice once I had paid the amount he asked for full and final settlement as far I am concerned?

 

[SydneyGeek]

Talk to an accountant. My gut feel would be no, he blew it, but there may be a legal position that a tax accountant or lawyer would know more about.

Also, please don't post in bold or all caps, it looks like shouting and you are likely to get ignored.

Denis