Mixed Excel data in MS Query 1

[paulcedarhill]

I have mixed data in a field that is queried from a excel 2000 table. Even after formating the cells as text, the cells that are inputted as numbers do not display in the query results. Only after I insert the ' prefix in the data field do they display. Is there a setting somwhere to modify this?

 

[xlbo]

This is fairly standard for SQL Server databases (don't know about others). They don't like mixed data type fields. A filed should be either alpha or numeric - you are asking for trouble having a mixture

the ' is converting your numbers into strings which is why it works

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[paulcedarhill]

Thanks Geof,

I thought that the "format as text" also converted the numbers to strings. I guess I was wrong.

 

[xlbo]

if you apply the format after you have entered the data it will make no difference. If you format as text and then enter the data it should work.

If this is not an option, you can use the TEXT formula on the data eg.

=TEXT(A1,"0")

copy down for all your data then copy and paste special>values and you should have a column of data that is treated as text

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question