我想用暂存表中的数据填充一个表。暂存表中感兴趣的列的数据类型为text
,但在其他情况下会填充可解析为双精度的值或空字符串(即"4.209"
、"42"
或""
)。目标表中的相应列的数据类型为double
。
我正在执行的SQL语句是
insert into dest (.., theColumn, ... ) select ...., theColumn, .. from src
当我执行该语句(使用ADO)时,我收到一个Data type mismatch in criteria expression
错误)。
如果我用null
替换theColumn
,它可以正常工作。所以,我想我应该以某种方式将空字符串转换为null
s。这可能吗?
使用IIf()
表达式:如果theColumn
包含表示有效数字的字符串,则返回该数字;否则返回Null。
SELECT IIf(IsNumeric(theColumn), Val(theColumn), Null) FROM src
我的第一个冲动是使用IsNumeric()
。然而,我意识到这是你要求的更直接的翻译。。。
SELECT IIf(theColumn='', Null, Val(theColumn)) FROM src
将空字符串转换为零也可以。
insert into dest (.., theColumn, ... ) select ...., theColumn+0, .. from src
您可以使用Val将"转换为0:
insert into dest (.., theColumn, ... ) select ...., Val(theColumn), .. from src
要插入Null,请使用以下函数:
' 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