Calculating VAT (Sales Tax)

[Switchwork]

URGENT!!!!!! Calculating VAT (sales TAX)

Hello, First I must say thank you for all the help I have received from this forum and the others.(Tables, etc.)

The new question I have involves calculating in reports. I have set up an parameter query to generate an invoice. I have done a report based on the parameter query and it works well. I have done it so it calculates a subtotal of all the jobs we have done for the customer (basically what they owe us Without VAT [sales tax})

Basically Now I need to add VAT (as it is called in England and Sales Tax in other parts of the world) The VAT in England is 17.5% and I have to add this on to the subtotal. Please I need help to do this. I know how to calculate the VAT on paper when I have been doing the Invoices manually. Can I use the same formula that I use to calculate manually on the computer or does it have to be a different way???

I have it written down somewhere and I will write the formula that i use on paper to get the VAT and then the Grand Total when I do the invoices manually and send it in and maybe someone can tell me if I am able to use the same formula to do the VAT on access??

In the meantime if anyone has the answer now don't hesitate to let me know ok as I need to sort this out ASAP. I did have a formula to try out however when I did try it out it gives me to many figures like 345.0987876 ya get me and I need to get it to look like money ya get me??

PLEASE PLEASE PLEASE PLEASE HELP ME!!! I gotta have this sorted by 1:00 in the afternoon tomorrow to start the invoices on access.

PLEASE AGAIN
THANXX xxxxx!!!!!xxxxx

 

[Switchwork]

Yes" Just me bringing the formula that I promised. The formula I use to calculate the VAT on paper. Now will I be able to use this on Access as a formula to calulate VAT?? If so then How do I do it?? If not then HOw else can I do it and what else can I do.

Right this is the formula that I use to work out VAT on paper before I do the invoices manually ::: Subtotal * 17.5 / 100 = VAT then I add the VAT to the SUBTOTAL to get the GRAND TOTAL

Can I use this in access is there a way??? THERE MUST BE A WAY!!!! If I can't use this way is there another way to calcualate the vat and then add it to the report. I basically need to know how to do the formula.

THanx and Please Help me I have got a deadline to meet.

THANX AND THANX AND THANX in advance....

 

[Rich]

In a query or calculated text box Vat:[SubTotal]*[VatRate] TotalAmount:[Vat]+[SubTotal]

 

[Switchwork]

Thank you thank you thank you!!!

Is it really that simple??

I tried something like that before but got loads of numbers. like 125.231546 I need it to look like currency ya get me?? If it comes out like this again doing it the way you suggested is there a way to make it like currency??? As it won't be from a table I can't set the text type can I?? Is there another way of doing it???

Please I know this is probably easy stuff to you but you have have helped me so much and I really appreciate it.

Thanx for all your help maybe I can repay the favour one day. Well maybe I am not that brainy.

 

[Rich]

You can set the Format for the field in the query and on the Report via the property sheets for both

 

[Switchwork]

Thanx again but I don't seem to understand what you mean by property sheets could you let me know what they are??

Thank you for taking time to answer me again I really appreciate it. And I apologise again for the mistake I made in the beginning I won't do it again now that I know.

Thanx Rich

 

[neileg]

In design view for your report, click the text box that holds your calculated figure. Then click the menu button that looks like a hand pointing to a page. That pops up the property page. Set the format for your text to currency.

Alternatively, you can set this to currency in your query, either by using the format command in the calculation or by setting the format for that column in your query.

I would set the format on the report.

 

[Rich]

You'll also almost certainly have to use a Rounding function too, otherwise you'll end up with something like 10 decimal places in your result

 

[Switchwork]

How do I do the Rounding function???

I would just like to clarify something: Is it better to run the calculation just in the report or in query or both???

This is probably a stupid question but I have been told so many different things from books and things that I just want to know for sure which is the best way to do it.

Thanx for your patience people and respect for sharing your expertise with me I really do appreciate it.

If you could just answer this query for me Id be most grateful and may leave you alone (for a little while at least)

Thank you again

 

[Rich]

=MyRoundFunction([SubTot]*[VatRate]) will give the answer.

In theory it's more efficient to have the calculation on the Report, it depends on the number of records and the complexity of the query as to wether you'll notice any difference

 

[Switchwork]

Hello me again. Thanx for that I know i said that I would leave you alone but I didn't know that I would run into trouble so I will apologise now in advance if you are getting annoyed with my dumbness???!!!


Before I put the formula in that you have recommended me to use for the "Round Function" I want to sort out the problem I have got. The problem I have got is getting the Grand total. I don't know if this is because the formula i done is wrong i don't know. THe VAT is working. When I do the formula do I do this?

The first formula to generate the VAT I do like this :

=sum ([totalcost])*0.175

The second formula to generate the Grand Total I do like this := [vat]+[totalcost]

Do I need the double brackets in the last formula??

Have I got the right decimal conversion ??

E.G. The VAT is 17.5% if converted to decimal is 0.175 is this right??
Please could you let me know this RICH. Maybe I can then get it right.

WHAT AM I DOING WRONG????

Thanx for letting me know that its done best in the report and not in the query. What do books know init?????

 

[Rich]

=Sum(([TotalCost]*.175) + [TotalCost])

 

[Switchwork]

Thank you so much

This formula is to get the grand total then yes???

So the one I am using to get the VAT (cos you gotta put the VAT amount on the Bottom of an invoice) is right??

I am gonna wait for your reply then try it on my database hopefully it will work now.

When I was trying to get the VAT amount It was giving me the wrong result. The reason I thought I was doing it wrong is because on a calculator you multiply the total by 17.5 and then DIVIDE the answer by 100 and you get the VAT. I thought it would be similar doing it in ACCESS.

See when I check the VAT result that ACCESS coughs up against the calculator I come up with a different VAT amount. WHY IS THIS???????? PLease let me know. I know i have been on your case but you have really helped me out and i am extremely grateful

I MEAN IT!!!!

THANX RICH
FROM SWITCH

 

[Rich]

.175 is 17.5/100 ?

 

[Switchwork]

Thank you so much Rich.
If I could I would give you cuddles and kisses but you are to far away .

I know you probably think I haven't got any sort of brain in my head but I have (Could've fooled you init?)
Thing is I haven't been around anything that remotely used any brain power for since I left school at 16 I am now 25.

I took the wrong path in life and have just found my way back to the crossroads and starting down the right sorta road. So its just a case of reviving the brain cells that I frazzled during those years. Ya get me??

Do you think I should have an Invoice table so I can keep track of invoices paid and not paid??? Any ideas will be appreciated. I haven't thought of what to do about keeping track of payments so if you know of a good way of doing it I would like to hear them.


If I have any more questions I will be here later but for now I better get on and sort this out so we can start invoicing again.


I will let you get on now if you could just answer the last query when ever you can No rush for this one ok

 

[Switchwork]

BIG QUERY

Hello only me again

Right I have worked out the VAT business and It is working pretty proud of myself even though is was RICH who done most of the work.

But now I have a problem with calculating the TOTAL TO BE PAID

I add the formula in the text box and when I run the report I get subtotal and VAT amount however I don't get anything with the TOTAL TO BE PAID not even an error message.

The formula is obviously wrong yes?? Could someone please help me with the GRAND TOTAL FORMULA???

I need to add Subtotal to the VAT amount that has been calculated.

Example

Subtotal £600.00
VAT£ 105.00
TOTAL TO BE PAID

I don't even get £0.00 I need it to calculate all together sort of thing.

I won't know the VAT amount so I can't calculate the amount with the subtotal,

I have tried =Sum ([subtotal]+[VAT]) but still got no answer. I also tried =Sum (([Totalcost]*0.175)+Totalcost)

Could someone please tell me where I am going wrong!!
I really though I had it sorted when the VAT worked out right and then thought that calculating the GRAND TOTAL would be easier BOY WAS I EVER WRONG!!!

So if anyone wants to give me one last push in the right direction I would be most grateful

Thanx peoples.

 

[Switchwork]

Where is everyone ?? Please help me with this last thing

As soon as this is sorted I will be away . I just really need the formula that I was chatting about above Please someone out there help me!!!!!

I IS BEGGING YOU,,,..,.,.

 

[R6Flyer]

Set the data source of the TOTAL TO BE PAID field to:

=[subtotal]+[VAT]

You don't need the sum part

 

[Switchwork]

I HAVE FINALLY DONE IT!!!!!

Hello everyone just a little thing to say that I have finally done it. I know it took a bit of asking but its done and it works. I do have some other questions bout invoices but they don't belong in this forum so I will be moving on to another one>

Thanks to all you people that helped me with this though. You don't know exactly how much I appreciate it believe me!!

Anyway I would appreciate some more help with my other querys so look forward to hear ing from you>

THANx and respect.