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?

On 20 Dec 2006 19:57:48 -0800, "p" <(E-Mail Removed)> wrote:

>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]

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?


John Vinson wrote:
> On 20 Dec 2006 19:57:48 -0800, "p" <(E-Mail Removed)> wrote:
>
> >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]

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
http://I.Am/DougSteele
(no private e-mails, please)


"p" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
>
> John Vinson wrote:
>> On 20 Dec 2006 19:57:48 -0800, "p" <(E-Mail Removed)> wrote:
>>
>> >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]
>