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

MattCole

New Member
Joined
Jun 22, 2010
Messages
5
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?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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.
 
Last edited:

MattCole

New Member
Joined
Jun 22, 2010
Messages
5
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!:cool:)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,121
Messages
5,570,307
Members
412,318
Latest member
angoeyuan
Top