Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-09-24
7
Medium Priority
?
147 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 22

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 500 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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 500 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 52

Accepted Solution

by:
Gustav Brock earned 1000 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 22
ID: 41820483
you're welcome ~ happy to help
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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