Query decimal place problem

G

Guest

Hi, I've made myself a database to keep a record of my sales and purchases
invoices. On a main form [Invoices] there is a SubForm [Invoice Sub Form]
which is based on a query containing 5 fields from a table called [Invoice
Sub Table], InvoiceID, Product, Amount, UnitPrice, VAT_Rate and 2 calculated
fields - ExVAT: Round([UnitPrice]*[Amount],2) and VAT_Amount:
Round([VAT_Rate]/100*[ExVAT],2).

The trouble I'm having is I would like the VAT_Amount field to formatted to
2 decimal places with out rounding up.

E.g. Amount of 1 x UnitPrice of £20.10 x VAT_Rate of 17.5% = 3.5175
It is rounded to 3.52, I want it to be 3.51

In the Invoive Sub Table
The Amount is a single number
UnitPrice is Currency
Vat_Rate is Single number

Any help will be greatly appreciated
 
G

Guest

If ýou wont get any better way then you can try

Round(([VAT_Rate]/100*[ExVAT])-0.005 ,2)
 
G

Guest

That's perfect. Thanks All.

Steve Schapel said:
MelDude

Try this...
Int([VAT_Rate]*[ExVAT])/100

--
Steve Schapel, Microsoft Access MVP

Hi, I've made myself a database to keep a record of my sales and purchases
invoices. On a main form [Invoices] there is a SubForm [Invoice Sub Form]
which is based on a query containing 5 fields from a table called [Invoice
Sub Table], InvoiceID, Product, Amount, UnitPrice, VAT_Rate and 2 calculated
fields - ExVAT: Round([UnitPrice]*[Amount],2) and VAT_Amount:
Round([VAT_Rate]/100*[ExVAT],2).

The trouble I'm having is I would like the VAT_Amount field to formatted to
2 decimal places with out rounding up.

E.g. Amount of 1 x UnitPrice of £20.10 x VAT_Rate of 17.5% = 3.5175
It is rounded to 3.52, I want it to be 3.51

In the Invoive Sub Table
The Amount is a single number
UnitPrice is Currency
Vat_Rate is Single number

Any help will be greatly appreciated
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Blank Fields in Report 2
Criteria not working on expression field in query 3
SQL Query Double Counts on Multi-Table Query 4
Problem - can create an updatable query 2
Problem in datatype in calculation 5
ASP.NET FormView Money Data Type Binding Problem with 2 Decimal Points.. 0
Decimal places in a temporary table 3
Create invoices/cheques for batches 5

Top