CODEWITHSUNDEEP
sqlvbams-access
Marcus Höglund
Marcus Höglund

Reputation: 16801

Vba - Convert number to decimal when using SQL on Ms Access

Trying to convert a number value to decimal, with two decimals (,00), in vba when selecting value from MS Access table. The Quantity column is of type number and the code Im using to format it is 

Dim rstBody As DAO.Recordset
Dim orderBodyQuery As String
orderBodyQuery = "SELECT distinct CONVERT(Decimal(9,2), Quantity) FROM " + mainTable + " WHERE [" + uniqOrderColumn + "] = """ + order + """"
Set rstBody = CurrentDb.OpenRecordset(orderBodyQuery, dbOpenSnapshot)

This results in the error:

Undefined function 'CONVERT' in expression

As the error describes Im guessing Im using the wrong syntax here (SQL Server) but I can't find how to do this. Please help

Upvotes: 0

Views: 5450

Answers (2)

Gustav
Gustav

Reputation: 55816

For display (text) it would be: 

Format([Quantity], "0.00")

To retrieve numeric values rounded by 4/5 to two decimals, it would be: 

CCur(Format([Quantity], "0.00"))

To set the Format property, use: 

"0.00", or just "Standard".

Upvotes: 2

Profex
Profex

Reputation: 1390

Surprise! Access doesn't use T-SQL.

I think you want the FORMAT() function.

What are differences between access sql

techonthenet.com/access/functions/

Upvotes: 0

Related Questions