How do I convert an empty string into a numerical null in Access SQL_

I want to populate a table with data from a staging table. The interesting column in the staging table has the datatype text but is otherwise filled with either values that are parsable as doubles or are the empty string (ie "4.209", "42" or ""). The according column in the destination table has the data type double.

The SQL Statement I am executing is

insert into dest (.., theColumn, ... ) select ...., theColumn, .. from src

When I execute the statement (using ADO) I receive a Data type mismatch in criteria expression error).

If I replace theColumn with null, it works without error. So, I figure I should somehow convert empty strings to nulls. Is this possible?

Answers


Use an IIf() expression: if theColumn contains a string which represents a valid number, return that number; otherwise return Null.

SELECT IIf(IsNumeric(theColumn), Val(theColumn), Null) FROM src

My first impulse was to use IsNumeric(). However I realized this is a more direct translation of what you requested ...

SELECT IIf(theColumn='', Null, Val(theColumn)) FROM src

Convert empty strings to zero maybe also work.

insert into dest (.., theColumn, ... ) select ...., theColumn+0, .. from src

You can use Val to convert "" to 0:

insert into dest (.., theColumn, ... ) select ...., Val(theColumn), .. from src

To insert Null, use a function like this:

' 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

Need Your Help

How do I get Eclipse to find the "org.eclipse.team.cvs.ssh" bundle?

eclipse repository m2eclipse amazon-iam

I've been trying to install IAM or m2eclipse without success. Both of them need the org.eclipse.team.cvs.ssh bundle, but Eclipse Helios complains that the repository could not be found. I've been