SQL Query in Excel shows blank for some values

Question

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.

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

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!

What's the .NumberFormat on these cells?

Ralph
– Ralph

2017-03-08 13:43:14 +00:00
Commented Mar 8, 2017 at 13:43 — Ralph
– Ralph, Commented Mar 8, 2017 at 13:43
I've set the .NumberFormat to "@".

May Ann
– May Ann

2017-03-08 13:46:40 +00:00
Commented Mar 8, 2017 at 13:46 — May Ann
– May Ann, Commented Mar 8, 2017 at 13:46

May Ann · Accepted Answer · 2017-03-10 08:10:38Z

1

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.

answered Mar 10, 2017 at 8:10

May Ann

518 bronze badges

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

Collectives™ on Stack Overflow

SQL Query in Excel shows blank for some values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related