VAT causing lots of numbers after the point

[Simon]

How can i set the database only to store and display number like £2.68
abd not like £2.6781568459


Thanks

 

[Guest]

"databases" don't really store and display numbers. Numbers are stored in
tables and displayed in queries, tables, forms, reports, and code. You can
set your data type of the field in your table to currency or decimal to limit
the number of decimal places.

 

[Guest]

I assume from the subject title of the thread that this results from adding
VAT to a price net of VAT or computing the VAST for a net price. If you are
doing this in an unbound computed control on a form then set the control's
Format property to Currency. If you are adding the VAT to the net price and
storing the value in a separate column in a table then set the data type of
that column to Currency. BTW storing the net price Plus VAT or the VAT
charged in a column is not redundancy if this is at transaction level as the
rate of VAT may well change and you'd want the gross price in the table to
remain the value at the time of the transaction and not reflect subsequent
changes in the rate of VAT. Don't store the gross price *and* VAT though;
that would be redundancy.

Note that the currency data type has a precision to four decimal places,
although you see it rounded to two. This can result in what appear to be
slight discrepancies in aggregated values. This is because the aggregation
is undertaken on the underlying values at the greater precision than that to
which you see the individual amounts rounded, i.e. its eliminating cumulative
rounding errors.

Ken Sheridan
Stafford, England

 

[Jamie Collins]

If you are adding the VAT to the net price and
storing the value in a separate column in a table then set the data type of
that column toCurrency.

thecurrencydata type has a precision to fourdecimalplaces,

Not necessarily: IIRC for intra-EU transactions values have to be five
decimal places in some circumstances. Then there's the effect of
integer division, rounding algorithm, unreasonably large value range,
etc.

FWIW I use DECIMAL(p, s) where p is a reasonable amount (allow a value
of 922,337,203,685,477.5807 and one day you'll get it) and s is one
extra decimal place than actually required in order to perform custom
rounding.

BTW storing the net price Plus VAT or the VAT
charged in a column is not redundancy if this is at transaction level as the
rate of VAT may well change and you'd want the gross price in the table to
remain the value at the time of the transaction and not reflect subsequent
changes in the rate of VAT.

Not necessarily: if you keep a history of VAT rates (i.e. start and
end dates for each rate) then you could always calculate the VAT
amount.

Don't store the gross price *and* VAT though;
that would be redundancy.

Note that 'redundancy' as regards storing calculated values is not as
clear cut as you seemingly make out e.g. there may be a performance
justification to storing calculated values. See:

http://www.dbazine.com/ofinterest/oi-articles/celko4
Calculated Columns by Joe Celko

"You are not supposed to put a calculated column in a table in a pure
SQL database. And as the guardian of pure SQL, I should oppose this
practice. Too bad the real world is not as nice as the theoretical
world..."

I assume from the subject title of the thread that this results from adding
VAT to a price net of VAT or computing the VAST for a net price.

Ken, although it's admirable of you in a way, I don't think you can
*prescribe* a schema -- data types (decimal scale, inherent rounding
algorithm, etc), table structure (history table or current state table
for VAT rates), strategy for storing calculated values, etc -- based
on the OP's mention of the single word 'VAT'

Jamie.

--