Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Can anyone help me fix counting VAT problem?

    I have a very small problem with counting VAT (tax). Following is the situation:

    The Decimal Places: on NetAmount field and VAT field are set as 'Auto' and it was previously Set as '2' and in both cases after counting different expenses it makes the NetAmount and then when I count the VAT on the NetAmount in the 'Detail' section of the Report for example on 23.00 it gives me the VAT @ 17.5% as 4.03 which is fine and on 28.16 which is of course made of a few expenses it gives me the VAT as 4.93 which means the total VAT for should be equal to 8.96 but when I Sum both the above VAT Totals in the ClientIDFooter it gives me the total as 8.95 which means 0.01 pence less which is not a big deal but when it comes to accountancy it causes problems.

    I would be most grateful for any assistance.
    Last edited by Emal; 12-06-08 at 09:36.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at this
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2006
    Posts
    386
    With regards to your comments are
    you making certain you are using a rounding function on the the data.. you need to make certain that the grand total is the same as the sum of its parts. if you are doing any calculations make certain that each element ROUND()s to the correct decimal place, and totals using thise values use the ROUND()ed value
    Do you mean by ROUND()S and ROUND ()ed that the decimal place for all the related files which I calculate should be the same? If so, Yes I have set all related fields (amounts, currencies, expenses etc) to 2 and also to Auto and to 3, but none of them worked and the Grand Total is differnt then sum of its parts. If ROUND() is something else, I would be grateful if you could please guide me to the right direction.

    This is how I have set the Control Source of the GrandVATTotal
    =Sum([VAT])
    and the Format like this
    #,##0.00;($#,##0.00)
    I have attached an example of VAT calculation in which you can see the difference in GrandTotal calcultion.

    Thank you so much.
    Attached Thumbnails Attached Thumbnails VAT Example.bmp  
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no thats not what you are doing at all, you are not rounding the the numbers at any stage.
    you are setting a display format in the report.
    unless you are explicitly using the round function (or similar functions) then irrespective of how you are displaying information the backing data is in more than two decimal places.

    every time you do a calculation then you MUST use the round function to fix the values to the appropriate number of decimal places. so when you do your VAT calculation make certain you round() the calcualtion

    the display format is irrelevant to the fundamental problem, it merely masks the problem

    in the absence of a rounding rule both calculations are correct
    line 1 £23.00 VAT @17.5% = 4.025 with your display format it shows 4.03 (but the data is 4.025)
    line 2 £28.16 VAT @17.5% = 4.928 with your display format it shows 4.93 (but the data is 4.928)

    4.928+4.025 = 8.953 with your display thats 8.95

    what you should do is round the number to the desired number of places, that way round the number stored woill change, NOT just the display format

    but the summ
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2006
    Posts
    386
    Sorry to be nuisance but do you mean that I should change the Decimal places for each field to 2 in the backend? if this is what you mean, I have already done that in the back end but it is still not working. If this is not what you mean by Round Function, then I would be most grateful if you could please advise how to get some information regarding the use of Round Fuction. I could not find this in the help menu. Thank you for your time!
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    NO
    look at the help system.. there is a function called ROUND, which ROUNDs the specified value to the specified number of places.

    eg
    ItemVAT = round(itemcost*VATRate,2)

    this should be used anytime you do a calculation in a report or form which may generate more decimal places than you need, especailly if you are using those values in a sum in the report.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2006
    Posts
    386
    I am afraid there wasn't much explained about ROUND FUNCTION expcet two lines on when to use it but with no examples or at least clear explanation. Being very new to this function I couldn't even understand as to where and how to use this function on my report and form sepcially when raising an invoice (report). Thank you for your time and inputs, you have been very patient with me.
    Emi-UK
    Love begets Love, Help Begets Help

  8. #8
    Join Date
    Sep 2006
    Posts
    265
    Here's my solution:

    You convert all your values to a (long) integer by multiplying them by 100 and then dividing them by 100. Along the lines of:

    Vat = CLng(.[Sale Amt] * .[Sale Vat Rate] * 100) / 100

    Simon

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    VATAmount = round([SaleValue] * .[VatRate],2)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •