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

Question

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

Is the error "Data type mismatch in criteria expression"? You may have a mixture of numeric and non-numeric values in AW_NUMMER (maybe some cells which look blank but are not actually blank) — Tim Williams
– Tim Williams, Commented Mar 7, 2022 at 7:54
So I am getting error if the column AW_Nummer does not contains 0 , means query is expecting the zeroes so that query should Ignore it but if there are no zeroes in the Aw_nummer column I am getting data mismatch error. — Karan Arora
– Karan Arora, Commented Mar 7, 2022 at 9:18
I am getting error in this line - objRecordSet.Open objRecCmd, , adOpenKeyset, adLockOptimistic — Karan Arora
– Karan Arora, Commented Mar 7, 2022 at 9:19
Then you almost certainly have some non-null but non-numeric values in your AW_NUMMER column... — Tim Williams
– Tim Williams, Commented Mar 7, 2022 at 16:37

Tim Williams · Accepted Answer · 2022-03-09 01:35:29Z

0

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

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

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

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.

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

answered Mar 9, 2022 at 1:35

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

5 Comments

Karan Arora Over a year ago

Thats an amaing analysis , thankyou so much . So I have to trim the extra spaces?

Karan Arora Over a year ago

so I started triming the the AW_Nummer column but getting an error.

Tim Williams Over a year ago

You need to remove all non-numeric values from that column: note even an empty string (which is not even a space) will be a problem for your query.

Tim Williams Over a year ago

Exactly how did you try that, and what error did you get? You can edit your question to add an explanation if it's too much for a comment.

Tim Williams Over a year ago

Here's a related post about clearing out these types of values: stackoverflow.com/questions/28990443/…

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related