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

peejay

Board Regular
Joined
Jul 11, 2003
Messages
83
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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/
 
Last edited:
Upvote 0
use COALESE to ensure you return a result
 
Upvote 0
At the very least, do you have IMEX=1 in the connection string?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
what is the actual SQL statement i.e SELECT to the end of WHERE
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top