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.

 

[peejay]

Hi RoryA

I've added IMEX=1 to the connection string, but it didn't help. And after researching what it does (and testing it) I couldn't use it as it converted my numeric columns/fields to text when they pasted to the target workbook.

 

[peejay]

hi mole999

SQL is as follows:

[CODE ]
SELECT
[Engagement Name]
,[Any Eng detail changes]
,[Original Eng Id if this has been changed]
,[Pricing Model]
,[Started]
,[Completed]
,[Estimated Fee]
,[Invoice Numbers This Month]
,[Invoiced xGST This Month]
,[Status]
,[Details of engagement changes]
FROM
[Engagements$]
WHERE
[Status] NOT IN ('Closed', 'Defer') AND
[Month Added] <= DATEVALUE('28/02/2018')
[/ CODE]

Connection string is:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\My File Name.xlsm;Extended Properties="Excel 12.0;HDR=Yes";

Thanks, Paul

 

[mole999]

what is the field that has blanks, and an example of what might be a default value

 

[peejay]

Hi Mole99

The last field [Details of engagement changes] is the problematic one. Generally the field (spreadsheet column) is empty; it will only have a value (as a text comment) as an exception; something like "Price has been updated".

When I dropped a value in the field's 1st record - just a "." character - the field was populated correctly, but without a value in it in record 1, I get nothing.
I'm just wondering if Excel is defaulting the field's type based in the first record and when record 1 is empty (possibly) treating the field as numeric and then ignoring the text as it's not a number? Only a semi-educated stab in the dark.

 

[mole999]

try

,[Details of engagement changes]

to

,CASE WHEN [Details of engagement changes] ='' THEN 'No Comment' ELSE [Details of engagement changes] END As [CHANGES]

 

[Fazza]

I guess that won't work: because I think the field is considered numeric. Same idea (that it won't work) if trying IIF(field is null, null, field). It only makes sense if the field is text: which is isn't...

Any chance of sorting the source data before running the query? By having some text in the top most rows of the field, it can be interpreted as text and all will be OK. Or maybe have a (maybe temporary?) copy of the data somewhere else that is sorted like this as a workaround.

I wonder if the entire source data has many null entries? And maybe a different structure for the table - even to multiple tables instead of one - might be good.

 

[peejay]

Fazza and Mole999

Many thanks for your time and help with this.

I think I've solved this now finally - I've used IF(LEN([Details of engagement changes])<>0, [Details of engagement changes], '') instead and this looks to be doing the trick after a simple test.

 

[mole999]

Glad you found a way