CODEWITHSUNDEEP
sqlms-access
Jolien .A
Jolien .A

Reputation: 173

Ms ACCESS and SQL: round to two decimals through query

I use queries to calculate all kinds of supplier information (average lead time, total spend for that supplier, average price, etc.). All output is shown in listboxes in forms in Ms ACCESS.

Example of a calculated number:

enter image description here

How do I format the output of these queries to be rounded to two decimals? I've been playing around with the listbox settings but cannot find it there. I believe I will have to do it in the query itself, but I'm not sure how.

Query code for the above number:

SELECT Avg([Item Master].PlannedLeadTime) AS AverageLeadTime
FROM [Item Master]
WHERE ((([Item Master].DateStamp)>=[Forms]![History Supplier Tool]![List2] And ([Item Master].DateStamp)<=[Forms]![History Supplier Tool]![List3]) AND (([Item Master].SupplierName)=[Forms]![History Supplier Tool]![List1]));

Note: List1 is a listbox where the user can select a certain supplier (for which the calculations are performed) and list2 and list3 are dates the user can select (as to determine a date range for the calculations).

Upvotes: 0

Views: 8772

Answers (2)

Rahul Hendawe
Rahul Hendawe

Reputation: 912

Use ROUND() Function:

The ROUND() function is used to round a numeric field to the number of decimals specified.

SQL ROUND() Syntax:

SELECT ROUND(column_name,decimals) FROM table_name;

|Parameter    |Description
________________________________________________    
|column_name  |Required. The field to round.
|decimals     |Required. Specifies the number of decimals to be returned.

So, Your required query will be:

SELECT ROUND(AVG([Item Master].PlannedLeadTime),2) AS AverageLeadTime
FROM [Item Master]
WHERE ((([Item Master].DateStamp)>=[Forms]![History Supplier Tool]![List2]
      And ([Item Master].DateStamp)<=[Forms]![History Supplier Tool]![List3])                   AND (([Item Master].SupplierName)=[Forms]![History Supplier Tool]![List1]));

Upvotes: 2

mwurth
mwurth

Reputation: 78

Access SQL has a rich function set one of which is the round function.

Example:

SELECT Round(Avg([Item Master].PlannedLeadTime),2) AS AverageLeadTime
FROM [Item Master]
WHERE (...)

Further information: http://www.techonthenet.com/access/functions/numeric/round.php

Upvotes: 3

Related Questions