VAT incorrect since change!!

[james]

40.000

Please post the expression you're using and the datatypes of the relevant
fields. If you have Currency fields are you certain that the field value
is
what yo see? A Currency has four decimals even if only two are shown -
you
could concievably have 400.0049 and just see 400.

 

[salmanjavaheri]

Hi I have recently changed the VAT rate in an access database to the
new one.
This has mainly worked. However, for a very few certain net totals,
when calculating the VAT it adds a few extra pence.
For instance a net total of £400 would produce a vat total of £60.03,
when (at the new vat rate of 15%) it should just be £60.00. What is
possibly stranger is that a net total of £399.99 produces the correct
vat total!

Does this ring any bells for anyone?

TIA

 

[Stefan Hoffmann]

hi Salman,

This has mainly worked. However, for a very few certain net totals,
when calculating the VAT it adds a few extra pence.
For instance a net total of £400 would produce a vat total of £60.03,
when (at the new vat rate of 15%) it should just be £60.00. What is
possibly stranger is that a net total of £399.99 produces the correct
vat total!

Does this ring any bells for anyone?

Sounds like you're calculating the VAT for each position and summing it
up. Due to rounding errors this may happen. Calculate the VAT only once
for your totals.


mfG
--> stefan <--

 

[salmanjavaheri]

hi Salman,




Sounds like you're calculating the VAT for each position and summing it
up. Due to rounding errors this may happen. Calculate the VAT only once
for your totals.

mfG
--> stefan <--

Thanks, when you say each position do you mean each item? This figure
comes up when there's only one item in the invoice.

You are right it does calculate the VAT for each item in the items
table, but as I should have mentioned, even in the items table it
gives the incorrect £60.03 figure. I haven't yet deduced any further
how that calculation is being made (as I'm unfortunately working on an
MDE and there's a lot of vb which I'm unfamiliar with).
How could it be calculating this figure incorrectly?

 

[Stefan Hoffmann]

hi Salman,

Thanks, when you say each position do you mean each item? Yes.

You are right it does calculate the VAT for each item in the items
table, but as I should have mentioned, even in the items table it
gives the incorrect £60.03 figure.

Check the data type / field types. They should be either Currency or
Number(Double).

How could it be calculating this figure incorrectly?

This is a system inherent problem:

http://en.wikipedia.org/wiki/Floating-point#Accuracy_problems


mfG
--> stefan <--

 

[John W. Vinson]

Hi I have recently changed the VAT rate in an access database to the
new one.
This has mainly worked. However, for a very few certain net totals,
when calculating the VAT it adds a few extra pence.
For instance a net total of £400 would produce a vat total of £60.03,
when (at the new vat rate of 15%) it should just be £60.00. What is
possibly stranger is that a net total of £399.99 produces the correct
vat total!

Please post the expression you're using and the datatypes of the relevant
fields. If you have Currency fields are you certain that the field value is
what yo see? A Currency has four decimals even if only two are shown - you
could concievably have 400.0049 and just see 400.

 

[hor vannara]

Please post the expression you're using and the datatypes of the relevant
fields. If you have Currency fields are you certain that the field value
is
what yo see? A Currency has four decimals even if only two are shown - you
could concievably have 400.0049 and just see 400.

 

[salmanjavaheri]

40.000

Many thanks for the help, all relevant fields appear to be in currency
format. I am now going to take a close look at the database and find
out exactly where the value is being calculated.

 

[salmanjavaheri]

I have looked in all of the update queries and cannot find any vat
calculations, so i am 99% sure that the following code is the
culprit...this code is executed when a drop down list for items to be
invoiced is updated:

Private Sub Select_Performance_AfterUpdate()
On Error GoTo Select_Performance_AfterUpdate_Err

Dim ctlComm As Control, ctlVATRate As Control, ctlVAT As Control
Dim cboSelPerfce As Control
Dim ctlInvIssd As Control
Dim ctlInvoiced As Control
Dim ctlInvAmount As Control, ctlInvVAT As Control
Dim sglIntVAT As Single, sglDecVAT As Single
Dim sglVATCalc As Single

Set ctlComm = Me![Commission]
Set ctlVATRate = Me![VATRATE]
Set ctlVAT = Me![Comm VAT]
Set cboSelPerfce = Me![Select Performance]
Set ctlInvIssd = Me![Invoice Issued]
Set ctlInvoiced = Me![Man_Or_Artiste]
Set ctlInvAmount = Me![Invoice Amount]
Set ctlInvVAT = Me![Invoice VAT]

sglVATCalc = ctlComm * ctlVATRate
sglIntVAT = Int(sglVATCalc)
sglDecVAT = (Left$(((sglVATCalc - sglIntVAT) * 100), 2) / 100)
ctlVAT = sglIntVAT + sglDecVAT
ctlInvIssd = "II"
ctlInvAmount = ctlComm
ctlInvVAT = ctlVAT
ctlInvoiced = "M"

DoCmd.RunCommand acCmdRecordsGoToNew
cboSelPerfce.Requery


any ideas?

 

[John W. Vinson]

I have looked in all of the update queries and cannot find any vat
calculations, so i am 99% sure that the following code is the
culprit...this code is executed when a drop down list for items to be
invoiced is updated:

Private Sub Select_Performance_AfterUpdate()
On Error GoTo Select_Performance_AfterUpdate_Err

Dim ctlComm As Control, ctlVATRate As Control, ctlVAT As Control
Dim cboSelPerfce As Control
Dim ctlInvIssd As Control
Dim ctlInvoiced As Control
Dim ctlInvAmount As Control, ctlInvVAT As Control
Dim sglIntVAT As Single, sglDecVAT As Single
Dim sglVATCalc As Single

Set ctlComm = Me![Commission]
Set ctlVATRate = Me![VATRATE]
Set ctlVAT = Me![Comm VAT]
Set cboSelPerfce = Me![Select Performance]
Set ctlInvIssd = Me![Invoice Issued]
Set ctlInvoiced = Me![Man_Or_Artiste]
Set ctlInvAmount = Me![Invoice Amount]
Set ctlInvVAT = Me![Invoice VAT]

sglVATCalc = ctlComm * ctlVATRate
sglIntVAT = Int(sglVATCalc)
sglDecVAT = (Left$(((sglVATCalc - sglIntVAT) * 100), 2) / 100)
ctlVAT = sglIntVAT + sglDecVAT
ctlInvIssd = "II"
ctlInvAmount = ctlComm
ctlInvVAT = ctlVAT
ctlInvoiced = "M"

DoCmd.RunCommand acCmdRecordsGoToNew
cboSelPerfce.Requery


any ideas?

Well, that's a lot of code to do only a little work... I don't ordinarily
declare control variables just to set the value of the control!

This line is probably at the root of the problem:

sglDecVAT = (Left$(((sglVATCalc - sglIntVAT) * 100), 2) / 100)

Using string functions to extract a portion of a number is almost surely A Bad
Idea. Could you describe the VAT calculation algorithm? I'm not even certain
what this code is intended to accomplish, but taking the leftmost two bytes of
a number might be a flawed attempt to round it...

 

[salmanjavaheri]

I have looked in all of the update queries and cannot find any vat
calculations, so i am 99% sure that the following code is the
culprit...this code is executed when a drop down list for items to be
invoiced is updated:

Private Sub Select_Performance_AfterUpdate()
On Error GoTo Select_Performance_AfterUpdate_Err

   Dim ctlComm As Control, ctlVATRate As Control, ctlVAT As Control
   Dim cboSelPerfce As Control
   Dim ctlInvIssd As Control
   Dim ctlInvoiced As Control
   Dim ctlInvAmount As Control, ctlInvVAT As Control
   Dim sglIntVAT As Single, sglDecVAT As Single
   Dim sglVATCalc As Single

   Set ctlComm = Me![Commission]
   Set ctlVATRate = Me![VATRATE]
   Set ctlVAT = Me![Comm VAT]
   Set cboSelPerfce = Me![Select Performance]
   Set ctlInvIssd = Me![Invoice Issued]
   Set ctlInvoiced = Me![Man_Or_Artiste]
   Set ctlInvAmount = Me![Invoice Amount]
   Set ctlInvVAT = Me![Invoice VAT]

   sglVATCalc = ctlComm * ctlVATRate
   sglIntVAT = Int(sglVATCalc)
   sglDecVAT = (Left$(((sglVATCalc - sglIntVAT) * 100), 2) / 100)
   ctlVAT = sglIntVAT + sglDecVAT
   ctlInvIssd = "II"
   ctlInvAmount = ctlComm
   ctlInvVAT = ctlVAT
   ctlInvoiced = "M"

   DoCmd.RunCommand acCmdRecordsGoToNew
   cboSelPerfce.Requery

any ideas?

Well, that's a lot of code to do only a little work... I don't ordinarily
declare control variables just to set the value of the control!

This line is probably at the root of the problem:

    sglDecVAT = (Left$(((sglVATCalc - sglIntVAT) * 100), 2) / 100)

Using string functions to extract a portion of a number is almost surely A Bad
Idea.  Could you describe the VAT calculation algorithm? I'm not even certain
what this code is intended to accomplish, but taking the leftmost two bytes of
a number might be a flawed attempt to round it...

What do you mean by vat calculation algorithm?

 

[John W. Vinson]

What do you mean by vat calculation algorithm?

You're using some fieldname and variables which mean nothing to me to
calculate a VAT. I live in Idaho; we have sales tax but do not have a VAT.

I'm asking: How is the VAT calculated? More to the point, how SHOULD it be
calculated? What is the *intent* of the expressions in your query?

 

[salmanjavaheri]

You're using some fieldname and variables which mean nothing to me to
calculate a VAT. I live in Idaho; we have sales tax but do not have a VAT..

I'm asking: How is the VAT calculated? More to the point, how SHOULD it be
calculated? What is the *intent* of the expressions in your query?

Ah i see, VAT since last decemeber is at 15%, so the VAT of a net
total of £400 is 15% of £400 - £60. Therefore, VAT should be
calculated by 0.15 * Net Total. Then the grand total is net + vat.

I am currently editing the calulations above just to confirm that they
are actually calculating the vat.

 

[John W. Vinson]

Ah i see, VAT since last decemeber is at 15%, so the VAT of a net
total of £400 is 15% of £400 - £60. Therefore, VAT should be
calculated by 0.15 * Net Total. Then the grand total is net + vat.

I am currently editing the calulations above just to confirm that they
are actually calculating the vat.

Hrm. How about
= Round(Me!Comm * Me!VATRate,2)

as the control source of the VATrate textbox, with no code at all?

 

[salmanjavaheri]

Hrm. How about
= Round(Me!Comm * Me!VATRate,2)

as the control source of the VATrate textbox, with no code at all?

I was having problems implementing that code, so instead made the
following changes:

Dim sglIntVAT As Double, sglDecVAT As Double
Dim sglVATCalc As Double

sglIntVAT = sglVATCalc
sglDecVAT = 0

Which has done the trick, is this also a safe way of doing it?

However, this is actually all academic at present, as I am working on
an MDE, the MDB I was working on is unusable as it is too old, is
there a way to either alter those few lines of code, or disable them
and somehow implement the same task using form controls?

 

[John W. Vinson]

I was having problems implementing that code, so instead made the
following changes:

Dim sglIntVAT As Double, sglDecVAT As Double
Dim sglVATCalc As Double

sglIntVAT = sglVATCalc
sglDecVAT = 0

Which has done the trick, is this also a safe way of doing it?

If you can do the calculations to four decimal places (with rounding), I'd use
a Currency datatype rather than Double. Double gives you about 14 decimals of
precision (with roundoff error in the last of them) so you'll have a lot of
hidden numbers hanging around.

However, this is actually all academic at present, as I am working on
an MDE, the MDB I was working on is unusable as it is too old, is
there a way to either alter those few lines of code, or disable them
and somehow implement the same task using form controls?

A database can't be "too old". If it has problems... fix the problems. No, you
cannot edit the structure of your code or your forms in a MDE; maintaining the
mdb from which it was created is ABSOLUTELY ESSENTIAL.

 

[salmanjavaheri]

If you can do the calculations to four decimal places (with rounding), I'd use
a Currency datatype rather than Double. Double gives you about 14 decimals of
precision (with roundoff error in the last of them) so you'll have a lot of
hidden numbers hanging around.


A database can't be "too old". If it has problems... fix the problems. No, you
cannot edit the structure of your code or your forms in a MDE; maintaining the
mdb from which it was created is ABSOLUTELY ESSENTIAL.

The MDB that is available to me is an extrememly different version of
the current MDE that is actually in use by the user. I don't think I
would be able to make all the changes required to make it like the
MDE.

I am able to edit the forms and reports using the MDE Unlocker tool,
so I'm wondering if there's any way to make a form control that would
override the vb and perform the same function, i.e. input the vat
value.

 

[salmanjavaheri]

The MDB that is available to me is an extrememly different version of
the current MDE that is actually in use by the user.  I don't think I
would be able to make all the changes required to make it like the
MDE.

I am able to edit the forms and reports using the MDE Unlocker tool,
so I'm wondering if there's any way to make a form control that would
override the vb and perform the same function, i.e. input the vat
value.- Hide quoted text -

- Show quoted text -

I suppose I could always convert the MDE to an MDB (minus the vb)
using the unlocker tool and then hopefully copying over the vb from
the old MDB to the new MDB will work.

Many thanks for your help btw.

 

[Douglas J. Steele]

Is your problem perhaps that the database wasn't split in to a front-end
(containing the queries, forms, reports, macros and modules), linked to a
back-end (containing the tables and relations), so that your concern is that
the data in the MDB version is out of date?

If that's the case, make the necessary changes for the MDB file and remove
all the tables from it. This will be your front-end. Create a new MDB and
import the data from the current MDE. This will be your back-end. Link the
front-end MDB to the back-end MDB. Once you know it's working, convert the
front-end MDB to an MDE, and distribute it to all your users.

There's no point for the back-end to be an MDE: MDEs don't do anything for
data.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The MDB that is available to me is an extrememly different version of
the current MDE that is actually in use by the user. I don't think I
would be able to make all the changes required to make it like the
MDE.

I am able to edit the forms and reports using the MDE Unlocker tool,
so I'm wondering if there's any way to make a form control that would
override the vb and perform the same function, i.e. input the vat
value.- Hide quoted text -

- Show quoted text -

I suppose I could always convert the MDE to an MDB (minus the vb)
using the unlocker tool and then hopefully copying over the vb from
the old MDB to the new MDB will work.

Many thanks for your help btw.

 

[salmanjavaheri]

Is your problem perhaps that the database wasn't split in to a front-end
(containing the queries, forms, reports, macros and modules), linked to a
back-end (containing the tables and relations), so that your concern is that
the data in the MDB version is out of date?

If that's the case, make the necessary changes for the MDB file and remove
all the tables from it. This will be your front-end. Create a new MDB and
import the data from the current MDE. This will be your back-end. Link the
front-end MDB to the back-end MDB. Once you know it's working, convert the
front-end MDB to an MDE, and distribute it to all your users.

There's no point for the back-end to be an MDE: MDEs don't do anything for
data.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)








I suppose I could always convert the MDE to an MDB (minus the vb)
using the unlocker tool and then hopefully copying over the vb from
the old MDB to the new MDB will work.

Many thanks for your help btw.

There is already a backend as MDB and the front end as MDE...It's just
that a lot of the forms are different in the current MDE to the only
MDB front end that could be found.

On second thoughts, if vb can't be edited in an MDE, then both the MDB
and MDE should have the same vb code.

Then it is very likely that a conversion of MDE -> MDB followed by a
transfer of vb from the MDB -> new MDB would be successful. Is there
a quick and easy way to transfer ALL vb from one MDB to another?

thx