CODEWITHSUNDEEP
ms-accessvba
HarveyFrench
HarveyFrench

Reputation: 4568

CDec in Access SQL is not behaving the same as when used from Access VBA

According to MSDN the syntax to convert a number to decimal in VBA is

CDec(expression)

I often use conversion function in access SQL as well. eg Clng, Cint etc...

However when I use CDec I sometime get this error

Compile error:Wrong number of arguments or invalid property assignment

Consider:

In the immediate window

? cdec(round(0.00023,4))
0.0002 

? CDec(Round(0.12345678+0.00000001,6))
0.123457 

? CDec(Round(0.12345679,6))
0.123457 

? CDec(Round(0.12345679,6),10)
This gives the above error  (ie as expected as no parameter is allowed)

In a SQL query column

CDec(Round(0.12345678,6))
This gives the above error.

CDec(Round(0.12345678,6),2)
This WORKS and give the answer 0.     What does the parameter do!?

CDec(Round(0.12345678,6),2,1)
This gives the above error.

I guess when a function is called from access SQL it is using different code to the one used by VBA. However, I'm stuck and don't understand.

Help!

I'm using MS Access 2013: Build: 15.0.4727.1003 32bit

Harvey

Upvotes: 2

Views: 3801

Answers (1)

Gustav
Gustav

Reputation: 55816

Dennis Wilmar of MS tells, that this is a confirmed bug from Access 2003 - not up to be removed:

Error message when you use the CDec() function in an Access query

The suggested work-around is to wrap CDec in a custom function:

Function NewCDec(MyVal)
   NewCDec = CDec(MyVal)
End Function

Upvotes: 3

Related Questions