MS Access 2007中使用逗号更新次数失败

问题描述：

为什么我不能执行这个简单的更新查询：MS Access 2007中使用逗号更新次数失败

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

如果我的字段值设置为0.11（带小数点），更新查询成功执行。

我的Access表字段数据类型是Number。

这里是我的错误： “3144 - 在UPDATE语句的语法错误”

您可能希望看到这样一个问题：http://stackoverflow.com/questions/11565335/ms -access-database-with-number-fields-in-a-language- – Bobort

答

你需要写一个点不是逗号的，就像这样：

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

好的，但是如果我的值来自另一个源如表格。那么值仍然是0,11。我应该用点逗号来点替换吗？ – wiz6

源列的数据类型是什么？ –

@ wiz6这是一个语法问题。当您在VBA中编写SQL语句时，您必须**使用**点**作为小数点分隔符。你不能写'0,11' - 你必须写'0.11' –

答

使用点（“”）作为小数点分隔符，而不是逗号。

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

如果你正在构建的SQL命令，为了将数字转换为字符串使用Str$。它始终使用.作为小数点分隔符，并且不依赖区域设置。另一方面，Format$函数使用Windows区域设置（可能是逗号）中定义的小数点分隔符。

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

谢谢，简单又容易！ – wiz6

答

您可以使用此功能串联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中使用逗号更新次数失败

相关推荐