Excel query problem

[Jed Shields]

Hi guys,

I've got two spreadsheets: Provider Master and Provider Template. The Master contains all the data of multiple providers, the Template queries the Master and pulls in the data for a single provider through a drop down list.

Problem - most of the data is pulled across but the last 3 columns won't pull in the text. However, if I change the original text to numbers then it works. I can't see any differences in the formatting etc. All the blank cells in the returned query show a 0. However, in the cells where data should be pulled across (not every cell has a value in the master list) show as blank.

Any ideas?

 

[Jed Shields]

Just checked and the formatting is the same. I've even copy paste special format from a column that works to make sure ... still pulling in blanks. Very odd.

 

[Jed Shields]

Sorry for the delay, I hadn't check the post for a while.

F2 doesn't do anything.

There are no numbers in the cells it's pulling over - it's either an R,A or G as a code for Red, Amber or Green. The other info it pulls over is a description of the status ie why it's a Red, Amber or Green.

There's no formula to post as it's a query? Or do you want to see the query formula?

Connection String:

DSN=Excel Files;DBQ=Z:\Test 20110519\Provider Dashboard Master.xlsm;DefaultDir=Z:\Test 20110519;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Command Text:

SELECT DataInputAll.Provider, DataInputAll.Cat, DataInputAll.ProviderCat, DataInputAll.`2010 Q1`, DataInputAll.`2010 Q2`, DataInputAll.`2010 Q3`, DataInputAll.`2010 Q4`, DataInputAll.`2011 Q1`, DataInputAll.`2011 Q2`, DataInputAll.`2011 Q3`, DataInputAll.`2011 Q4`
FROM DataInputAll DataInputAll
WHERE (DataInputAll.Provider=?)

It works fine up to DataInputAll.`2011 Q1 and then it just shows a blank rather than the value.