Solved

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

Posted on 2016-09-24
7
74 Views
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)

Thanks
0
Comment
Question by:hindersaliva
  • 2
  • 2
  • 2
  • +1
7 Comments
 
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.

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
 
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
            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
 

Author Comment

by:hindersaliva
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

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 36

Assisted Solution

by:PatHartman
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.
0
 
LVL 49

Accepted Solution

by:
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.

/gustav
0
 

Author Closing Comment

by:hindersaliva
ID: 41820476
Thanks all!
0
 
LVL 19
ID: 41820483
you're welcome ~ happy to help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

828 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