CODEWITHSUNDEEP
sqlexcelvba
May Ann
May Ann

Reputation: 51

SQL Query in Excel shows blank for some values

I have a data on a sheet on excel and I use SQL to query to get a dataset. This is the column the query is returning.

Sample Data

However, only items on numeric format is shown, and if the item is alphanumeric, it is blank. The resulting query is below:

enter image description here

I have formatted this column as a text and below is my connection string:

objConnectionXL.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"

Used query string:

sQueryFilteredValues = "Select [Reference] From [Data$] Where " & sFilterCriteria

I also tried using the query string below but it resulted the same:

sQueryFilteredValues = "Select CSTR([Reference]) From [Data$] Where " & sFilterCriteria

Recordset:

objRecordsetXL.Open sQueryFilteredValues, objConnectionXL, adOpenStatic, adLockOptimistic, adCmdText

Where: adOpenStatic = 3, adLockOptimistic = 3, adCmdText = &H1

And I use below to copy the recordset into Excel

rngRange.Offset(1, 0).CopyFromRecordset objRecordsetXL

How can I show alphanumeric items on the recordset as well? Thanks!

Upvotes: 0

Views: 1790

Answers (1)

May Ann
May Ann

Reputation: 51

The problem is with the range I am copying the query results to. Only the source range has the text format. The format of the range I am copying the query results to is in general, so I guess Excel automatically converts the data into a number, based on the first cell value.

I resolved this problem by changing the format into text.

Upvotes: 1

Related Questions