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

Question

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

You should retrieve Quantity as is, then format the values when displayed by setting the format (property) of the textbox. — Gustav
– Gustav, Commented Oct 19, 2016 at 7:31
@Gustav Ok, I will try that. Do you know how I should use format to get the expected result with two decimals? — Marcus Höglund
– Marcus Höglund, Commented Oct 19, 2016 at 7:37
Yes. 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". — Gustav
– Gustav, Commented Oct 19, 2016 at 7:41
@Gustav Thanks. If you provide that as an answer I will accept that solution. — Marcus Höglund
– Marcus Höglund, Commented Oct 19, 2016 at 7:44

Gustav · Accepted Answer · 2016-10-19 07:47:24Z

2

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".

answered Oct 19, 2016 at 7:47

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Profex · Accepted Answer · 2016-10-18 21:10:28Z

0

Surprise! Access doesn't use T-SQL.

I think you want the FORMAT() function.

What are differences between access sql

techonthenet.com/access/functions/

answered Oct 18, 2016 at 21:10

Profex

1,3908 silver badges20 bronze badges

2 Comments

Marcus Höglund Over a year ago

The docs of CurrentDb.OpenRecordset says "The source can be a table name, a query name, or an SQL statement that returns records"..If i remove the convert and only select the quantity it works..So it is possible to send SQL

jleach Over a year ago

JET/ACE (the Access db engine) has it's own dialect of SQL, which doesn't include CONVERT. (T-SQL is the SQL Server implementation of the SQL language: every database engine has a slightly different dialect)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related