Data format syntax in Where Clause

[Nigel White]

I have the following SQL query to extract data from a Jade database using ODCB.

SELECT
Table1.Field1,
Table1.Field2,
Table1.Field3,
Table1.Field4,
Table2.Field5
FROM Table1, Table2
Where Table1.Field6 = Table2.Field7;

It returns zero rows even though there is data in both tables and Field6 and Field7 have matching values in both Tables. I suspect that the data format of Field6 is different to Field7, so I understand that I need to convert the format in the Where clause, however, I am unsure of the syntax. I think Field6 is being evaluated as a number and Field7 is being evaluated as a string. What syntax do I use to convert them both to a number or both to a string, please?

[Nigel White]

Is there not anyone out there that can answer this question, please?

[BeeJay]

Sorry, I'd help if I could but unfortunately SQL select statements into Jade is outside my area of knowledge as it's not something I've ever needed to use.

Cheers,
BeeJay.

[Nigel White]

Okay, we're nearly up to 300 views without any help.

I am happy for any suggestion, no matter how silly it may be.

[ghosttie]

Some flavours of SQL have a conversion function but I don't know if JADE's ODBC driver supports it. In any case using something like that would be pretty slow, why not make sure that the data formats for the fields are the same?

[Nigel White]

Thanks for your reply ghosttie. I have no control over the data formats. When I bring them into any software, say MS Access for example, the formats always end up the same and I can join the tables on those fields without issue, it only seems to be during an SQL Join that they don't match, and only for some table combinations, not all. The fields are record IDs and the values are structured like '0001234.0000000123', they always end up being numeric.