0

I am using the following VBA code to update a Double field in my db. The name of the field is week and the name of the value to put on is valore. In my VBA they are both String variable.

When valore is a number without decimal (es. "15"), I have no problem.

When valore is a number with decimal (es. "2.5" or "2,5"), I get the following error:

Syntax error in UPDATE statement

CurrentDb.Execute "UPDATE [Pianificazione risorse] " _
 & "SET " & week & " = " & CDbl(valore) & " " _
 & "WHERE [Codice Progetto] = '" & xCodice_Progetto & "' AND Risorsa = '" & xRisorsa & "';"
braX
  • 11,506
  • 5
  • 20
  • 33
Stefano
  • 209
  • 2
  • 10
  • 34

1 Answers1

2

Localization.

Try this in the immediate window:

? CDbl(1.5)
 1,5 

? CDbl(1,5)
*compile error*

Running an Italian version of Access, CDbl results, when converted to string, have a decimal comma. But Access SQL always expects decimal points (US format) for floating point numbers.

You should use parameters instead of concatenating strings:
How do I use parameters in VBA in the different contexts in Microsoft Access?

If you really must, use Gustav's CSql() function when concatenating variables with SQL.

... & CSql(CDbl(valore)) & ...
Andre
  • 26,751
  • 7
  • 36
  • 80