VAT Lookup Query

groovybunny

New Member
Joined
Apr 23, 2007
Messages
33
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!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,269
Office Version
365
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

New Member
Joined
Nov 5, 2009
Messages
2
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,288
Messages
5,485,912
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top