Ms ACCESS and SQL: round to two decimals through query
Jolien .A
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:
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).
mwurth
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Regular expression to load data from different files
- Next: How To Deal With Asynchronous Login to REST API?
Related
Related Related
- 1
MS ACCESS SQL Query on two tables
- 2
Compare two tables in MS Access SQL query
- 3
MS Access SQL Average function with decimals
- 4
Round numbers > two decimals
- 5
In MS Access SQL Server Linked Table pass through query
- 6
javascript round decimals till two
- 7
Convert SQl query to MS Access
- 8
MS Access SQL Insert Query
- 9
MS Access Database SQL Query
- 10
MS Access SQL Query statement
- 11
SQL Query PIVOT to MS Access SQL Query
- 12
Round to 2 decimals and pass through TextView
- 13
Round to 2 decimals and pass through TextView
- 14
MS-access Pass-Through Query to SQL Server 2008 Adding Prefix
- 15
MS Access query: between two numbers
- 16
Removing decimals in SQL Query
- 17
Getting the table structure in ms access with SQL query?
- 18
MS Access SQL query for IP address range
- 19
MS ACCESS SQL QUERY COUNT DISTINCT
- 20
MS Access Date() Function and SQL Server Query
- 21
SQL query cannot be imported in MS Access 2010
- 22
If Then Else in WHERE clause in MS Access SQL Query
- 23
Getting the table structure in ms access with SQL query?
- 24
MS Access Date() Function and SQL Server Query
- 25
MS Access SQL query for IP address range
- 26
SQL INSERT INTO SUBFROM from QUERY in ms access
- 27
Measuring MS Access SQL query duration
- 28
error of sql query in MS Access database
- 29
Combining Rows in MS Access SQL Query
Comments