MS Access 2007 update number with commas fails

Question

Why can't I execute this simple update query:

SQL = "UPDATE Table SET field=0,11 WHERE id=12456"
db.Execute SQL, dbSeeChanges

If I set the field value to 0.11 (with decimal point), the update query executes successfully.

My Access table field datatype is Number.

Here is the error I get:

"3144 - Syntax error in UPDATE statement."

You may want to see this question: http://stackoverflow.com/questions/11565335/ms-access-database-with-number-fields-in-a-foreign-language — Bobort, Aug 02 '16 at 18:52

Olivier Jacot-Descombes · Answer 1 · 2016-08-02T20:42:13.843

Use a dot (".") as decimal separator instead of a comma.

SQL = "UPDATE Table SET field=0.11 WHERE id=12456"

If you are constructing the SQL command, use Str$ in order to convert a number to a string. It always uses . as decimal separator and does not depend on regional settings. The Format$ function on the other hand, uses the decimal separator defined in the regional settings of Windows (which might be a comma).

SQL = "UPDATE Table SET field=" & Str$(x) & " WHERE id=" & id

score 1 · Answer 2 · answered Aug 02 '16 at 18:52

1

You need to write a dot instead of a comma, like this:

SQL = "UPDATE Table SET field=0.11 WHERE id=12456"
db.Execute SQL, dbSeeChanges

answered Aug 02 '16 at 18:52

SQL Police

4,127
1
25
54

Okay, but if my value comes from another source like table. Then the value is still 0,11. Should i make some comma to point replacement? – wiz6 Aug 02 '16 at 19:09
what's the data type of the source column? – Mark C. Aug 02 '16 at 19:35
@wiz6 It is a syntax issue. When you are writing a SQL statement in VBA, the you **must** use a **dot** as decimal separator. You cannot write `0,11` - you must write `0.11` – SQL Police Aug 02 '16 at 19:39

score 0 · Answer 3 · answered Aug 03 '16 at 09:47

You can use this function to avoid this and most other troubles when concatenating SQL:

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function

MS Access 2007 update number with commas fails

3 Answers3

Linked