Import NULL into Access from Excel

[cstimart]

I have been trying o use the following Tutorial to manipulate data I have.
http://www.datawright.com.au/excel_resources/excel_access_and_ado.htm


When I get to the point where the data is moved from Excel to Access, the following code section crashes when it hits a date field that has no value.

    For i = 2 To Rw
        rst.AddNew
        For j = 1 To 15
            rst(Cells(1, j).Value) = Cells(i, j).Value
        Next j
        rst.Update
    Next i

How can I change the code to fill the access field with null?

 

[ChrisOswald]

Hi,

So long as you don't assign a value to a field in a new recordset, it'll be auto assigned to NULL, or possibly it's default value from the table design (I'm not certain off hand). I'm explicitly having it insert NULL in this snippet.

    For i = 2 To Rw
        rst.AddNew
        'For the purpose of illustration, let us assume that columns 1,4,7
        'are date fields.
        For j = 1 To 15
            Select Case j
            Case 1, 4, 7
                'OPTION 1
                'depending on your data, this should work if you're
                'just missing values, but everything is formatted as a date.
                If IsDate(Cells(i, j).Value) Then
                    rst(Cells(1, j).Value) = Cells(i, j).Value
                Else
                    rst(Cells(1, j).Value) = Null
                End If
 
                'OPTION 2
                'if you need to worry about dates being formatted as numbers,
                'or if there could be text instead,
                'you could use this sub snippet instead.
                If IsNumeric(Cells(i, j)) Or IsDate(cells(i,j)) Then
                    'test seperately to avoid type mismatches
                    If Cells(i, j) > 0 Then
                        'you might need to test for really big values..
                        rst(Cells(1, j).Value) = cDate(Cells(i, j).Value)
                    Else
                        rst(Cells(1, j).Value) = Null
                    End If
                Else
                    rst(Cells(1, j).Value) = Null
                End If
 
            Case Else
                rst(Cells(1, j).Value) = Cells(i, j).Value
            Next j
        End Select
        rst.Update
    Next i

If you wanted to be a bit fancier, you could use an error handler, but they are very easy to misuse, so it's considered generally best to avoid their use in VBA.

    For i = 2 To Rw
        rst.AddNew
        For j = 1 To 15
            On Error GoTo DataEntryHandler
            rst(Cells(1, j).Value) = Cells(i, j).Value
            On Error GoTo 0
        Next j
        rst.Update
    Next i
    Exit Sub
DataEntryHandler:
    Select Case Err.Number
        'you'll need to find the potential error numbers
        'for type mismatches and place them here
    Case 1, 2, 3
        Resume Next
    Case Else
        MsgBox "Error: " & Err.Description _
               & Chr(13) & "Please contact owner", _
               vbCritical, _
               "ERROR " & Err.Number
        End
    End Select

 

[xenou]

I already answered the question earlier today:
https://www.mrexcel.com/board/index.php?posts/2836803/

 

[cstimart]

OK...so I'm trying all these fixes with no success. So I tried the ErrorHandler version and get the following error when the code hits the first empty date field...

Item cannot be found in the collection corresponding to the requested name or ordinal.

Any ideas?

 

[Joe4]

Duplicate: http://www.mrexcel.com/forum/showthread.php?t=572614

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).