Excel field coming through as a Number when really a String 1

[michbell]

I'm using CR9.

I have a report that uses a table from Excel 2003 and one from Informix. These two tables are linked by the field {part_nbr} which is a string. It had been working fine for more than a year, but just recently it stopped recognizing the link. It says that the data types are not compatible and when I look at how the Excel field is labeled in CR, it says it's a number, even though if I look in the spreadsheet itself, it's labeled as a text field. The field does include many that contain only numbers (9080651, 9081245, etc.) but some contain a letter as well (9081265K). I can't change the format of the field from Informix, it has to stay a string.

I've read through other threads about this issue, and I've tried putting XXXXXX in the first row of that field in Excel, but it still doesn't work. This spreadsheet is edited a lot, so I'd rather not move it to Access. Any ideas? Thanks in advance.

 

[synapsevampire]

If the first row contains a number, then Excel lies

Insert a false row in the beginning of the spreadsheet containing a string, such as "LIAR!!!!"

Make sure it's always the first rwo.

Excel is a horrible datasource, as you're discovering.

Another approach would be to LINK (not import) the spreadsheet into an Access database and create a query in Access as the datasource, you can also link the Informix datasource in there.

This resolves several problems:

You can create a query in Access for the Excel file that ALWAYS converts it to a string.

Access will be faster than Crystal

The data source is now available to other programs as well.

-k

 

[michbell]

Thanks Synapsevampire! Creating a link in Access worked perfectly!!!!! I appreciate it!!