Help! Text and Numbers in database Query from another worksheet?

[MattCole]

Hi - hope someone can help.....
I have made a database query to return data from another excel worksheet (all in excel 2003, and on Windows XP)
It's fine EXCEPT that some of the columns in the external datasource worksheet contain text rather than numbers. The query returns these as blank while the columns with number in the fields (even when they are a result of a formula) come back as numbers fine.
I've tried formatting the columns as text in both the extrnal source and the 'recipient' worksheet but no difference.
Help! It seems straightforward...but perhaps it's not?

 

[p45cal]

Because excel is not a database, a given field (column) can contain data of varying types. The query will look at the top 8(?) rows of of the data it's querying to decide what data type to return. Whichever data type is the most prevalent in that top 8 rows is what the query returns for the wholecolumn. It won't return different datatypes in a given column.
So make sure that the source data has the appropriate data type in the top 8 rows (cell formats will be important here too; text, number, date etc.)
I got round this by putting in 5 or more rows of dummy data of the required type at the top of the source data and hiding those rows.

 

[MattCole]

Hi p45Cal, thanks a lot for that, I'll check this out hopefully today and post an update. (PS I can always be bothered with replying on forums!)

 

[MattCole]

Hey, just to say that this worked a dream - thanks so much.