0

I am using MS Access as back end and inserting the values that are in excel trying to handle empty spaces.

sSQL = "Insert into " & Cells(1, 1) & " VALUES ( '" &  CStr(Cells(r, 53)) & "',' " & CStr(Cells(r, 54)) & "', '" & IIF(Isnull(Cells(r,55), "NULL",Cint(Cells(r,55)) & "',' " & CInt(Cells(r, 56)) & "',' " & CInt(Cells(r, 57)) & "',' " & CInt(Cells(r, 58)) & "','" & CInt(Cells(r, 59)) & "',' " & CInt(Cells(r, 60)) & "',' " & CInt(Cells(r, 61)) & "')"

For the IIF(Isnull(Cells(r,55), "NULL",Cint(Cells(r,55)) if the cell is empty it should return NULL in the query but it is while running the query is not displaying NULL in it.

Insert into Test VALUES ( 'MODULE:','   Test','' ,' 0',' 0',' 0','0',' 0',' 0')

How can I get NULL over there. Macro is throwing an error while inserting coz there is no value for that.

Improve this question

3 Answers 3

Reset to default
3

First, you should probably look into using ADO or DAO's OpenRecordSet() to directly insert data in the database rather than constructing your SQL by hand. It will be much more robust.

However, in your case here, your iif() doesn't work probably because:

Cells(c,r) default property Value is a Variant that returns Empty if the cell doesn't contain anything. It doesn't return Null.

So you should use IsEmpty() instead of IsNull().

You could also use this short IsBlank() VBA function to make sure your test for an empty cell works as expected for various cases.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I just tried using an ADODB.Command parameter query from an Excel Module and it seems to work, too:

Option Explicit

Sub AccessInsert()
    Dim con As ADODB.Connection, cmd As ADODB.Command
    Set con = New ADODB.Connection
    con.Open _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\Users\Public\Database1.accdb;"
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandText = _
            "INSERT INTO [" & Cells(1, 1) & "] " & _
                "(FirstName, LastName, DOB) " & _
                "VALUES (?,?,?)"
    cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255)
    cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255)
    cmd.Parameters.Append cmd.CreateParameter("?", adDate, adParamInput)
    cmd.Parameters(0).Value = IIf(IsEmpty(Cells(3, 1)), Null, Cells(3, 1))
    cmd.Parameters(1).Value = IIf(IsEmpty(Cells(3, 2)), Null, Cells(3, 2))
    cmd.Parameters(2).Value = IIf(IsEmpty(Cells(3, 3)), Null, Cells(3, 3))
    cmd.Execute
    Set cmd = Nothing
    con.Close
    Set con = Nothing
End Sub
Improve this answer

Comments

0 
sSQL = "Insert into " & Cells(1, 1) & " VALUES ( '" & CStr(Cells(r, 53)) & "',' " & CStr(Cells(r, 54)) & "', " & IIf(IsBlank(Cells(r, 55)), "Null", CStr(Cells(r, 55))) & ",' " & CInt(Cells(r, 56)) & "',' " & CInt(Cells(r, 57)) & "',' " & CInt(Cells(r, 58)) & "','" & CInt(Cells(r, 59)) & "',' " & CInt(Cells(r, 60)) & "',' " & CInt(Cells(r, 61)) & "')"

Able to get NULL in the query by using the above query along with IsBlank function.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.