calculate VAT

[kryten]

Hi everyone,

I have a form with a control to calculate VAT, however since books are zero rated for VAT I need to calculate it for some items but not for others. Here's the code I used:

If [Books] = False Then
[VAT] = [LineTotal] * 0.175
Else
[VAT] = 0
End If

[Books] is a yes/no check box.

This kinda works, when a book is entered into an order the VAT is £0.00 (good so far), however if you then enter an item that isn't a book into the same order the VAT is calculated for the non-book item and the book's VAT changes from £0.00 to the same value as the VAT for the non-book item, even though the two items are not the same price and so should have different VAT values.

Any assistance would be greatly appreciated.

 

[Mile-O]

Having Books as a checkbox rings an alarm bell in my head. It screams, to me at least, that you have a repeating group in your table which goes against the rules of database normalisation. i.e. fields like Books, Electronics, Confectionary, Magazines, etc. where all are checkboxes. Bad move. Is this the case?

If not then

I wouldn't hard code the VAT rate into your database.

Put a field in your product table called VAT or something. Make it a Yes/No field.

While others may not suggest this due to how often the VAT rate changes, I'd make a new table for storing the VAT with the structure:

tblVAT
VATID
DateExpired
VATRate

If so

I'd research normalisation before going any further

 

[Rich]

You should store the VAT rate applicable to the item along with the item invoice details

 

[Mile-O]

You should store the VAT rate applicable to the item along with the item invoice details

You've always got to do it the logical way, don't you?

The alarm bells are still ringing though.

 

[Rich]

Yes, there appear to be several problems with krytens structure

 

[ChrisO]

“and the book's VAT changes from £0.00 to the same value as the VAT for the non-book item”

What’s the chance that [VAT] is an unbound control on a continuous form?

Write to one…write to all.

 

[kryten]

Having Books as a checkbox rings an alarm bell in my head. It screams, to me at least, that you have a repeating group in your table which goes against the rules of database normalisation. i.e. fields like Books, Electronics, Confectionary, Magazines, etc. where all are checkboxes. Bad move. Is this the case?

Thanks for your reply.

There is just one checkbox field for books, everything else has the same VAT rate. I just needed to identify which products were not liable for VAT.
If I were to take your advice and make new VAT table how could I then calculate the VAT for everything but books?

 

[Mile-O]

If I were to take your advice and make new VAT table how could I then calculate the VAT for everything but books?

Don't take Rich's. His will manage future products with a new VAT code shoudl it ever happen lettng you rollback your calculations.

 

[Rich]

Don't take Rich's. His will manage future products with a new VAT code shoudl it ever happen lettng you rollback your calculations.

quantify please

 

[scouser]

Vat?

Rather than start a new thread I thought I would add to this one.
I have tried to implement your suggestion 'SJ McAbney' but as usual have confused myself.

I added field to tblProducts = VAT set this to Yes/No

I then created tblVAT as suggested. I set VatRate to number.

I put the VAT control on frmProducts. If the user checks the VAT control I want the unitPrice to include VAT. I tried this (but got an error) 'invalid or unqualified reference'

Private Sub VAT_AfterUpdate()
If .VAT = True Then
.UnitPrice = .UnitPrice * VAT
End If
End Sub

Any ideas?

Also what would I do if the VAT changed?

Basically how do I apply the current VatRate from tblVAT to the Products?
If the VAT changed how would the procucts be updated + how will this affect historic data?

Sorry for being dumb.........just can not visualise it!!

I have attached DB so you can see I HAVE tried!!!
Phil.

 

[scouser]

VAT How to Apply?

Just keeping this thread alive!!!
Still not able to visualise whats required!!
Cheers,
Phil.