VAT Calculation

[Simon Glencross]

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? It also does
not seem to be rounding as it did with =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) I am now using =CCur(Round([Sum Of
RealisedPrice]*DLookup("Vat","tblVAT"))) but it is not returning the same
value as before??!

Any help would be much apprecaited.

S

 

[Squirrel]

What is the field definition for the data? "Changing to 1" - does that mean
you've defined an integer?

 

[Simon Glencross]

Ok I have changed the field definition to a general number and that seems to
have stopped it changing to 1 bit it is not rounding as it did before, any
ideas?

What is the field definition for the data? "Changing to 1" - does that
mean you've defined an integer?

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? It also
does not seem to be rounding as it did with =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) I am now using =CCur(Round([Sum Of
RealisedPrice]*DLookup("Vat","tblVAT"))) but it is not returning the same
value as before??!

Any help would be much apprecaited.

S

 

[Douglas J. Steele]

You can't replace the two values in the old statement with a single value
like that.

You'd have to do two lookups, one to give you the 1.175 and one to give you
the 2. On the other hand, since you doubtlessly always want 2 decimals,
hard-code the 2, and have the DLookup simply return 1.175

 

[Simon Glencross]

Hi Doug,

When you say hard code this where do you mean in tblvat in field VAT under
format general and decimal place 2?

Will this round the figure as did =CCur(Round([Sum Of
RealisedPrice]*1.175,2))

Many Thanks

S

You can't replace the two values in the old statement with a single value
like that.

You'd have to do two lookups, one to give you the 1.175 and one to give
you the 2. On the other hand, since you doubtlessly always want 2
decimals, hard-code the 2, and have the DLookup simply return 1.175

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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? It also
does not seem to be rounding as it did with =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) I am now using =CCur(Round([Sum Of
RealisedPrice]*DLookup("Vat","tblVAT"))) but it is not returning the same
value as before??!

Any help would be much apprecaited.

S

 

[Marshall Barton]

Simon Glencross wrote:\

When you say hard code this where do you mean in tblvat in field VAT under
format general and decimal place 2?

Will this round the figure as did =CCur(Round([Sum Of
RealisedPrice]*1.175,2))

Is that what the ,2 was for? In our other thread, I thought
the ,2 was a part of the VAT rate. Now that I see what you
intended, remove the ,2 from the table field and put it
after the DLookup(...)

=CCur(Round([Sum Of RealisedPrice]*DLookup(...), 2))

 

[Douglas J. Steele]

No, setting the format doesn't change the value of the field at all: it just
changes how it's displayed.

I meant have DLookup("Vat","tblVAT") return only 1.175, and then use

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

When you say hard code this where do you mean in tblvat in field VAT under
format general and decimal place 2?

Will this round the figure as did =CCur(Round([Sum Of
RealisedPrice]*1.175,2))

Many Thanks

S

You can't replace the two values in the old statement with a single value
like that.

You'd have to do two lookups, one to give you the 1.175 and one to give
you the 2. On the other hand, since you doubtlessly always want 2
decimals, hard-code the 2, and have the DLookup simply return 1.175

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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? It also
does not seem to be rounding as it did with =CCur(Round([Sum Of
RealisedPrice]*1.175,2)) I am now using =CCur(Round([Sum Of
RealisedPrice]*DLookup("Vat","tblVAT"))) but it is not returning the
same value as before??!

Any help would be much apprecaited.

S