VBA SQL ignores a field's values if the first record has the field empty

[peejay]

Hi
I'm running an ADODB SQL query in Excel, pasting the results to another sheet. One of my text fields won't paste any results when the field is empty for the first record. If I have a value in the first record, it works fine.

I've tried using CAST and IIF statements on the field to try and get it to work, but I've run out of ideas. It's probably a simple fix but my brain's started to hurt now, so I'm hoping someone out there can pass on a solution please.

 

[Fazza]

What else is in the field?

 

[Fazza]

Cause i'm wondering if not all the other records in the field have text.

"Mixed data type" fields are a problem. If every other record has text, there should be no issue. But if some records have numbers then it can be a problem. Plenty of old forum threads on the subject.

https://www.mrexcel.com/forum/excel...y-excel-database-excel-2000-text-numbers.html

https://www.mrexcel.com/forum/excel...ternal-data-missing-data-problem-refresh.html

http://dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

 

[mole999]

use COALESE to ensure you return a result

 

[RoryA]

At the very least, do you have IMEX=1 in the connection string?

 

[peejay]

What else is in the field?

Hi Fazza

The column (field) just has text in a couple of rows.

 

[peejay]

Hi Mole999

I did try COALESCE but I kept getting an error when running the SQL (one of those really helpful - not - messages!), so gave up on it.

 

[peejay]

I'll have to check RoryA (it's on my work laptop).
My query is accessing data in ThisWorkbook to process and copy to another one. If it doesn't, I'll add it and retry.

btw what does IMEX=1 actually mean? The code to run the SQL I've sourced via Google and can make it run but don't have a great understanding of what it means.

Thx Paul

 

[Fazza]

For IMEX=1, please refer to the links I posted above. regards

PS. This may not be the issue for you. Difficult to diagnose with limited info.

 

[mole999]

what is the actual SQL statement i.e SELECT to the end of WHERE