Access 2016 ADO SQL - INSERT a 'null' date value

I have some Data/Time fields in a table in Access, where I have set it to Required = No.

When I fire off an INSERT (via ADO) to insert data it doesn't like it if there is no Date. ie. Blank.
In my solution there will be 'blanks' in these Date columns. I want to INSERT a blank.

Has somebody got any ideas on how to work around this please?

(I'd like to try and avoid having a dummy date like 01/01/1900 to represent blanks)

Thanks
hindersalivaAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
sSQL = Replace(sSQL, "# 0#", "NULL")

Open in new window

If your code produces string like "# 0#" for "blank" entries, you should fix it as you wish to avoid dummy dates for Null values.
One method could be to apply my function at an earlier state as the function is intended for exactly that.

/gustav
0
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
in the SQL, test the date and if it is null, do not include it.  Post the procedure that constructs the SQL so we can help you with this if you need it.

example:
dim vFields as variant
dim vValues as variant
dim sSQL as string

vFields = null
vValues  = null

sSQL = "INSERT INTO [MyTablename] ("

with me.mycontrolname 'assuming you are in Access and this is code behind the form where values are collected
   if not isnull(.value) then
      vfields = (vFields + ",") & "[" .Name & "]" 'assuming control name is the same as fieldname -- otherwise, use ControlSource instead of Name if form is bound
      vValues = (vValues + ",") &.value 'number
      'or
      'vValues = (vValues + ",") & """" & .value & """" 'text
      'or
      'vValues = (vValues + ",") & "#" & .value & "#" 'date
   end if
end with

Open in new window

'repeat for all controls

then
if not isnull(vFields) then
   sSQL  = sSQL  & vFields & ")" & " SELECT " & vValues & ";"
   ' ... then the code to execute
end if

Open in new window


alternately, you can try:
sSQL = ... & NZ(me.controlname.value, "Null") ... 

Open in new window

in the SQL value part for each control that may not have a value, assuming this is Access and code is behind the form. This example is for a number -- include delimiters if data type is not a number. Excel does not recognize NZ (Null-to-Zero with optional second argument to use if true) but Access does.
0
 
Gustav BrockConnect With a Mentor CIOCommented:
You can use this function to build your SQL strings:
' 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

Open in new window

See the in-line comments for example usage.

/gustav
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
hindersalivaAuthor Commented:
I tried this. It seems to work.

NOTE: my SQL statement is constructed in Excel VBA.

        'to fix any blank dates
        sSQL = Replace(sSQL, "# 0#", "NULL")

Open in new window

0
 
PatHartmanConnect With a Mentor Commented:
If you post the whole procedure that builds the sSQL, we can tell you what you are doing wrong.  It is better to not create the problem than it is to fix it after the fact.
0
 
hindersalivaAuthor Commented:
Thanks all!
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.