Crystal Reports 13: Data types are not compatible Excel

[former_member1340851]

Hello,

So, I was sent data from our Access database in Excel format to compare to a table within Crystal Reports 13 that comes from our ERP system. I’m getting the error 'data types are not compatible' when trying to link the Tool-Code from Excel data with the EQNO (Equipment Number) in the Crystal table field.

Column Headers from Excel Sheet

DIFF | FirstofCUST_NUM | Tool-Code| MoldDesc | Current Cycle Count | PMShotCount |Text19 | FirstofData-Run | Warranty | Last Checklist PM Count

Table from Crystal is called PMEQMT

The fields within this table that match what’s in the Excel Sheet are

Tool-Code: Called EQNO in Crystal Table field

MoldDesc:Called DESCRIP in Crystal Table field

Current Cycle Count: Called TOTAL_UNITS in Crystal Table field

FirstofData-Run:Called INST_DATE in Crystal Table field

So, I imported the Excel spreadsheet data into Crystal and selected the PMEQMT Table as well as the Excel Data.

I notice that the link between Tool-Code and EQNO does not link up and it gives me a message that says ‘Data Types are not compatible’.

For Example:

Tool-Code from Excel Spreadsheet is 1293 and the EQNO in Crystal is also 1293 but when I import the Excel Data Crytal reads the Excel data for Tool-Code as 1,293.00 intstead of 1293.

I'm thinking the Excel column has to be converted to a string formula? If so can anyone please give me any guidance as I'm new to this.

Thanks!

[DellSC · Accepted solution]

Is the field in the database a number field or a string field? I suspect what's happening is that it's a string field and the first rows of data in the spreadsheet are numbers, which makes Crystal think the Tool-code Excel column is numeric. That would cause an issue like this.

The easiest way to get around this would be to make sure that the first row of data has all text or a combination of text and numbers in Tool-code in the first row of data under the headers. Even if you end up filtering out that row for the report.

-Dell

[former_member1340851]

Okay, I figured out what I needed for now. I converted the Tool-ID to text using a simple Excel Formula =TEXT(A2,"0") , saved and then imported into Crystal. Now I"m working with formatting the data and will write a formula to address the missing tool-id's from the Access database compared to our ERP system data.

[former_member1340851]

Okay, I found out that I need to convert the Tool-Code field in Excel to a text field. I need to write a formula in Excel to do this and then I can proceed to link the Excel Data with the data from our ERP system in Crystal .

Anyone know how to write this formula in Excel? I'm going to research that now. Thanks!