Select query not reading alphanumeric values in VBA

Question

I am reading excel data using Select query in VBA but query is not reading the line items where Column "TEILENUMMER" is alphanumeric like 61615A27D68 but able to read the line items where "TEILENUMMER" is numeric like 61627356223.

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 = "/testexcel"


strquery = "Select [BUNO],[RECHNR],[RECHDAT] from [Tabelle1$] where ([RECHNR] ='" & StrInvoiceNumber & "' AND ([TEILENUMMER] Is Not Null) AND ([TEILENUMMER] <> 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

Does it work if you add double quotes around zero `[TEILENUMMER] <> "0"` — Salman A, Feb 22 '22 at 12:59
So I just want all the values except where TEILENUMMER is NULL or 0. that means I want values where TEILENUMME is numeric or alphanumeric. Above query is working perfectly only issue its not taking alphanumeric values — Karan Arora, Feb 22 '22 at 13:03
The driver has likely decided that TEILENUMMER is a numeric field (based on scanning the first x rows) and so any non-numeric values will get nulled out... Eg see https://stackoverflow.com/questions/31681032/excel-data-extraction-issue-with-column-data-type — Tim Williams, Feb 22 '22 at 19:43
Other related posts on SO: https://www.google.com/search?q=Microsoft.ACE.OLEDB.12.0+column+datatypes+site:stackoverflow.com — Tim Williams, Feb 22 '22 at 19:48
...if you set the Excel format for that column to "Text" (ie. not "General") that should fix it. — Tim Williams, Feb 22 '22 at 20:29

Select query not reading alphanumeric values in VBA

0 Answers0