Using SQL Command rather than linking

[Dirtless]

Morning;

I'm unable to link two tables, because the primary key in one is numeric, and string in the other. I'm using a command instead of setting the relation, but am getting a failure as the "ToNumber" function is not (I am presuming) a recognized function at the server level.

My command looks like this:

Select *
From "Datafile" [Dat]
full outer join "Clientfile" [Clients]
on "Dat"."ClientID"=Tonumber("Clients"."ID")

Can anyone tell me where I'm going wrong, or an alternate function to accomplish the same thing?

Thanks.

D

 

[briangriffin]

What is your database?

 

[Dirtless]

Brian;

OBCD if that's what you meant. It does not matter now, for the following reason:

In the command I had to remove the reference to the "Tonumber" function, after which it worked. In other words, while Crystal Reports will not allow me to link on fields with incompatible field types it will allow me (in a command) to join on those fields (without converting one to number or the other to string).

It's working now. Thanks for your effort.

D