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
  • 2
  • 2
  • 2
  • +1
LVL 19

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 49

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 34

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 49

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 19
ID: 41820483
you're welcome ~ happy to help

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Generating a graph via Excel 3 25
Problem to open text file 11 69
Add a range in an Excel graph 5 33
Excel Hyperlink Question 4 26
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now