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

Posted on 2016-09-24
Last Modified: 2016-09-28
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)

Question by:hindersaliva
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 21

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 125 total points
ID: 41814068
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.

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
      'vValues = (vValues + ",") & """" & .value & """" 'text
      'vValues = (vValues + ",") & "#" & .value & "#" 'date
   end if
end with

Open in new window

'repeat for all controls

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.
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 41814097
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
                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.


Author Comment

ID: 41814108
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 38

Assisted Solution

PatHartman earned 125 total points
ID: 41814279
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.
LVL 51

Accepted Solution

Gustav Brock earned 250 total points
ID: 41814422
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.


Author Closing Comment

ID: 41820476
Thanks all!
LVL 21
ID: 41820483
you're welcome ~ happy to help

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question