VBA uses dot for decimals but sql uses comma which leads to syntax error in UPDATE query.

[pablo40]

Hello everyone,

This is my first post on this forum and I sincerely hope someone can come up with solution to the problem I am facing.


I have the following issue:
- in VBA when defining a value to a variable (for example 10.5) decimals go with a dot (not comma), comma is not accepted as putting comma causes an error
- however when embedding such value in SQL statement a dot causes an error as SQL only accepts commas.
- I cannot replace dot with a comma when defining a value like for example 10.5 and changing it to 10,5 as it causes error


Interestingly there is no such conflict in Access 2013 (and the below query works) but in Access 2010 version the below query does not work.
I tried converting 10.5 to text and then 'wrapping up' this text with Cdbl(text). But it does not work.
Below is the code. And I would really appreciate if someone could help me on that.

Sub test()

'THE FOLLOWING WORKS WITH MSACCESS 2013 BUT DOES NOT WORK WITH MSACCESS 2010!!
'CONSEQUENTLY THE SAME QUERY BEHAVES DIFFERENTLY IN TWO ACCESS VERSIONS

poziom_agregacji = 2


SumaCostChargeIn = 345.2 'double number must have dots, comma is not accepted as if used then COMPILE ERROR IS PRODUCED
strsql = "UPDATE tblmastertable SET tblmastertable.costchargein=" & SumaCostChargeIn & " WHERE (((tblmastertable.[Levell])=" & poziom_agregacji - 1 & ")" _
& "AND ((tblmastertable.[ID])=" & 2 & ") AND ((tblmastertable.[treeid])=" & 255 & "));"

With CurrentDb 'is run for the first time it would be better to check to see if the
.QueryDefs.Delete ("AktualizujPodsumy") ' querydef exists and then delete it
Set qdfNew = .CreateQueryDef("AktualizujPodsumy", strsql) ' THIS THROWS SYNTAX ERROR IN UPDATE STATEMENT BECAUSE SQL USES COMMA AND ON THE OTHER HAND DOUBLE NUMBER USES DOTS
.Close
End With
DoCmd.OpenQuery "AktualizujPodsumy", acViewNormal, acViewPreview

End Sub

[pablo40]

I've managed to solve this problem!!
The solution is to replace SumaCostChargeIn with x where:

x = Replace(CStr(SumaCostChargeIn), ",", ".")

strsql = "UPDATE tblmastertable SET tblmastertable.costchargein= " & x & " WHERE (((tblmastertable.[Levell])=" & poziom_agregacji - 1 & ")" _
& "AND ((tblmastertable.[ID])=" & 2 & ") AND ((tblmastertable.[treeid])=" & 255 & "));"


I hope someone will find it useful!

[pbaldy]

Thanks for posting your solution, and welcome to the site!