'Ignore 0 values using select query but giving error if 0 values are not present

I don't want to extract 0 values from Excel using the select query in VBA. I have used the below mentioned code for the same and it is working, like if there is 0 values present the code it is ignoring that but if there are no 0 values present in the Excel I am getting an error. So my main motive is if 0 values are present then ignore it using select query and if there are no 0 values in the Excel column then its okay just ignore the null values only.

Dim objConn As Object
Dim objRecordSet As Object
Dim objRecCmd As Object

Set objConn = CreateObject("ADODB.Connection")
Set objRecCmd = CreateObject("ADODB.Command")
Set objRecCmd_Update = CreateObject("ADODB.Command")

strFolderPath = "\inputexcel"
strQuery = "Select [BUNO],[RECHNR] from [Sheet1$] where [RECHNR] ='" & StrInvoiceNumber & "' AND ([AW_NUMMER] Is Not Null And ([AW_NUMMER] <> 0))"

objConn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFolderPath & ";Extended Properties=""Excel 12.0;IMEX=1""")

'Set objConn.Open = objConn.Execute(Source, Options:=Options)
Set objRecordSet = CreateObject("ADODB.Recordset")

objRecordSet.CursorLocation = adUseClient

objRecCmd.ActiveConnection = objConn
objRecCmd.CommandText = strQuery

objRecordSet.Open objRecCmd, , adOpenKeyset, adLockOptimistic

If Not objRecordSet.BOF And Not objRecordSet.EOF Then
    objRecordSet.MoveFirst
End If
sqlexcelvba


Solution 1:[1]

Using the test code at the end of this post...

Source table #1 (a null and a zero, no errors - results at right):

enter image description here

Source Table #2 (no zeros, and no errors - results at right)

enter image description here

Source Table #3 (no zeros, but cell C3 has an empty string value, so it's not really null). Gives error Datatype mismatch in query expression because of that one non-null non-numeric value in C3.

enter image description here

Sub TestSO()
    
    Dim objConn As Object
    Dim objRecordSet As Object
    Dim objRecCmd As Object, strFolderPath, strQuery
    
    Set objConn = CreateObject("ADODB.Connection")
    Set objRecCmd = CreateObject("ADODB.Command")
    
    strFolderPath = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    
    objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFolderPath & _
                  ";Extended Properties=""Excel 12.0;IMEX=1"""
    
    strQuery = "Select [BUNO],[RECHNR],[AW_NUMMER] from [Sheet1$] where [RECHNR] ='" & "blah" & _
               "' AND ([AW_NUMMER] Is Not Null And ([AW_NUMMER] <> 0))"
    
    Set objRecordSet = CreateObject("ADODB.Recordset")
    objRecordSet.CursorLocation = adUseClient
    objRecCmd.ActiveConnection = objConn
    objRecCmd.CommandText = strQuery
    
    objRecordSet.Open objRecCmd, , adOpenKeyset, adLockOptimistic
    
    [F2].CurrentRegion.ClearContents
    If Not objRecordSet.BOF And Not objRecordSet.EOF Then
        [F2].CopyFromRecordset objRecordSet '.MoveFirst
    End If
    
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Williams

Related Questions