linking 2 fields which are different data types

[sue1127]

I'm using Crystal Reports 8.0 and MS Sql Server 7.0. I want to add a table to an existing report so that I can include the ledger account in the report. I want to link bank_register.document_number (datatype is int) with gl_cost_transactions.document_id1(datatype is char(25).

I was able to run it in query analyzer by converting bank_register.document_number to char(25) like this:

convert(char(25),bank_register.document_number) = gl_cost_transactions.document_id1)

But when I try it in Crystal, I get an odbc error:
Count field incorrect or syntax error

Is there any way to link these two fields in Crystal?

 

[synapsevampire]

I'd suggest that you make a View out of the SQL, right after you twist the ears off of the DBA...

You can use a Sub report in Crystal, or you might paste the SQL into an ADO connection, or you might edit the from/where clause to reflect this.

Again, a View is best, and very easy to create.

-k

 

[jcoleman]

I'am haveing the same problem only diff is that my tables are in two different databases. Can I create a view that links these two tables together? Does CR V10 have any extra abilities as far as linking tables together?

 

[sue1127]

Thanks, synapsevampire. I'm going to try the view. (I really can't blame our DBA this time--the field was set up by the vendor of the software package).

I'm not sure what you mean by pasting the sql into an ADO connection. Could you say a little more about that?

Thanks,

Sue