Comparing non-equal type fields in different tables

[p]

I have a custom database table which resides on a SQL server on the
local network. I am comparing some "job numbers" in an accounting
package against this table. The "job numbers" all have the same format
but unfortunately at the bottom of the accounting table there are a few
text entries which are not at all like the normal format for a job
number.

Accounting says it needs these and wont take them out. Is there a way I
can get a relationship going with two different tables when one is text
and the other is numbers?

 

[John Vinson]

I have a custom database table which resides on a SQL server on the
local network. I am comparing some "job numbers" in an accounting
package against this table. The "job numbers" all have the same format
but unfortunately at the bottom of the accounting table there are a few
text entries which are not at all like the normal format for a job
number.

Accounting says it needs these and wont take them out. Is there a way I
can get a relationship going with two different tables when one is text
and the other is numbers?

Unless you'll be multiplying, adding or dividing Job Numbers, I'd
really recommend making them all Text. Then you'll have no problem
joining.


John W. Vinson[MVP]

 

[p]

It's a problem of joining two different software packages over ODBC
where I cannot change the table type..

there is absol. no way i can do this?

 

[Douglas J. Steele]

Look at using CStr or Format to convert the numeric value to a string.

In other words, you'd have something like

WHERE CStr(Table1.Field1) = Table2.Field2

rather than

WHERE Table1.Field1 = Table2.Field2

Note that you can only do this by going into the SQL View of the query (and
you will then be unable to get back to the graphical Design view)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)