Calculation

[Simon Glencross]

I am trying to find how to display the difference between 2 calculations and
need a little help.

I have a Realisedprice field which displays net of VAT and I have another
field "TotalIncVAT" with the following as a source =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) which gives me the total including vat. What I
would like now is another field showing me the difference between the
realisedprice field and the TotalIncVat.


Thanks in advance.

S

 

[Marshall Barton]

I am trying to find how to display the difference between 2 calculations and
need a little help.

I have a Realisedprice field which displays net of VAT and I have another
field "TotalIncVAT" with the following as a source =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) which gives me the total including vat. What I
would like now is another field showing me the difference between the
realisedprice field and the TotalIncVat.

With the above calculations, you can just use a text box
with the expression:
=TotalIncVAT - Realisedprice

However, I should point out that placing the value 1.175,2
in a text box expression is not a best practice. The
problem is that when the VAT percentage chabes, you will
have to hunt through your application to find all the places
where that number was used. Far better to put that value in
a table so it only needs to be changed in one place, kind
of a normalixation rule for programming. Depending on other
considerations, the table may only need one row. With this
arrangement, your text boxes (and any code that needs it)
can get the value using expressions like:

=CCur(Round([Sum Of RealisedPrice]*DLookup("Vat","tblVAT")))

 

[Guest]

Sounds like you are breaking the basic database normalization rule of storing
calculated values in a table. There is enough discussion on this that I
won't bother with it other than to advise against it. The proper method is
always to perform the calculations from the static data when the result of
the calculation needs to be presented to the user. You will know why when
someday one of the values is incorrect and you can't figure out how it got
that way.

But, to answer your question, all you need is a text box field with the
control source
= Realisedprice + TotalIncVAT

 

[Simon Glencross]

Marshall,

Point taken! I have created a table called tblvat in this I have enterened
the value "1.175,2" but this keeps changing to "1" why is this?


Many thanks for your help!

S


My other question is how do

I am trying to find how to display the difference between 2 calculations
and
need a little help.

I have a Realisedprice field which displays net of VAT and I have another
field "TotalIncVAT" with the following as a source =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) which gives me the total including vat. What I
would like now is another field showing me the difference between the
realisedprice field and the TotalIncVat.

With the above calculations, you can just use a text box
with the expression:
=TotalIncVAT - Realisedprice

However, I should point out that placing the value 1.175,2
in a text box expression is not a best practice. The
problem is that when the VAT percentage chabes, you will
have to hunt through your application to find all the places
where that number was used. Far better to put that value in
a table so it only needs to be changed in one place, kind
of a normalixation rule for programming. Depending on other
considerations, the table may only need one row. With this
arrangement, your text boxes (and any code that needs it)
can get the value using expressions like:

=CCur(Round([Sum Of RealisedPrice]*DLookup("Vat","tblVAT")))

 

[Marshall Barton]

Probably because the field is defined as size Integer or
Long.

Are you stiil looking for the difference? If so, what about
my earlier reply didn't do what you wanted?
--
Marsh
MVP [MS Access]

Point taken! I have created a table called tblvat in this I have enterened
the value "1.175,2" but this keeps changing to "1" why is this?


My other question is how do
"Marshall Barton" wrote

I am trying to find how to display the difference between 2 calculations
and eed a little help.

I have a Realisedprice field which displays net of VAT and I have another
field "TotalIncVAT" with the following as a source =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) which gives me the total including vat. What I
would like now is another field showing me the difference between the
realisedprice field and the TotalIncVat.

With the above calculations, you can just use a text box
with the expression:
=TotalIncVAT - Realisedprice

However, I should point out that placing the value 1.175,2
in a text box expression is not a best practice. The
problem is that when the VAT percentage chabes, you will
have to hunt through your application to find all the places
where that number was used. Far better to put that value in
a table so it only needs to be changed in one place, kind
of a normalixation rule for programming. Depending on other
considerations, the table may only need one row. With this
arrangement, your text boxes (and any code that needs it)
can get the value using expressions like:

=CCur(Round([Sum Of RealisedPrice]*DLookup("Vat","tblVAT")))

 

[Simon Glencross]

Marshall,

The difference calcualtion is great thanks the problem I am having is with
the later part I have created a tblvat and added in by vat rate as 1.175
which is fine and I have sorted the integ out but my total which I get by
using =CCur(Round([Sum Of RealisedPrice]*DLookup("Vat","tblVAT"))) but this
does not give me the same total as =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) I'm not sure but I dont think it is rounding
anymore??

Any ideas?

S

Probably because the field is defined as size Integer or
Long.

Are you stiil looking for the difference? If so, what about
my earlier reply didn't do what you wanted?
--
Marsh
MVP [MS Access]

Point taken! I have created a table called tblvat in this I have enterened
the value "1.175,2" but this keeps changing to "1" why is this?


My other question is how do
"Marshall Barton" wrote

Simon Glencross wrote:

I am trying to find how to display the difference between 2 calculations
and eed a little help.

I have a Realisedprice field which displays net of VAT and I have
another
field "TotalIncVAT" with the following as a source =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) which gives me the total including vat. What I
would like now is another field showing me the difference between the
realisedprice field and the TotalIncVat.


With the above calculations, you can just use a text box
with the expression:
=TotalIncVAT - Realisedprice

However, I should point out that placing the value 1.175,2
in a text box expression is not a best practice. The
problem is that when the VAT percentage chabes, you will
have to hunt through your application to find all the places
where that number was used. Far better to put that value in
a table so it only needs to be changed in one place, kind
of a normalixation rule for programming. Depending on other
considerations, the table may only need one row. With this
arrangement, your text boxes (and any code that needs it)
can get the value using expressions like:

=CCur(Round([Sum Of RealisedPrice]*DLookup("Vat","tblVAT")))

 

[Marshall Barton]

Double check the DLookup by executing it in the
debug/immediate window.

What do you want it to round to, pound/dollar or
pence/penny? The way you have it it will round to
pound/dollar. If you want it to round to pence/penny, then
you need to specify that, i.e. Round(..., 2)
--
Marsh
MVP [MS Access]

The difference calcualtion is great thanks the problem I am having is with
the later part I have created a tblvat and added in by vat rate as 1.175
which is fine and I have sorted the integ out but my total which I get by
using =CCur(Round([Sum Of RealisedPrice]*DLookup("Vat","tblVAT"))) but this
does not give me the same total as =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) I'm not sure but I dont think it is rounding
anymore??

Probably because the field is defined as size Integer or
Long.

Are you stiil looking for the difference? If so, what about
my earlier reply didn't do what you wanted?

Point taken! I have created a table called tblvat in this I have enterened
the value "1.175,2" but this keeps changing to "1" why is this?


My other question is how do
"Marshall Barton" wrote
Simon Glencross wrote:

I am trying to find how to display the difference between 2 calculations
and eed a little help.

I have a Realisedprice field which displays net of VAT and I have
another
field "TotalIncVAT" with the following as a source =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) which gives me the total including vat. What I
would like now is another field showing me the difference between the
realisedprice field and the TotalIncVat.


With the above calculations, you can just use a text box
with the expression:
=TotalIncVAT - Realisedprice

However, I should point out that placing the value 1.175,2
in a text box expression is not a best practice. The
problem is that when the VAT percentage chabes, you will
have to hunt through your application to find all the places
where that number was used. Far better to put that value in
a table so it only needs to be changed in one place, kind
of a normalixation rule for programming. Depending on other
considerations, the table may only need one row. With this
arrangement, your text boxes (and any code that needs it)
can get the value using expressions like:

=CCur(Round([Sum Of RealisedPrice]*DLookup("Vat","tblVAT")))