Linked tables, data type different

[TEOA]

Hi I want to link two tables together, both tables has a field called opr_nbr
This is the operation number (05 for example)
The trouble is in one of the tables the data is a number in the other is text.
The fields are outputs from legacy systems, I want an easy way to link these tables so that I can perform queries on them.

the problem I find is im getting a data mismatch error, as the data is showing as not the same.

When looking at the tables one shows 05 and the other would show 5

is there a way of converting the field during a query. I am unable to modify the table itself, only query from it.

I have been scratching my head on this one for a few days, done some searches on here and still come up with nothing. Hopefully one of you guys can help.

Thanks

 

[pbaldy]

You can use the appropriate conversion function (CStr, Clng, etc) within the join:

FROM OneTable INNER JOIN AnotherTable ON OneTable.NumericField = CLng(AnotherTable.TextField)

or the other way around, as appropriate to your data. Note that this must be done in SQL view, and you will then have to work with the query in SQL view. That type of join can not be represented in design view. An alternative is to create a query that returns all the fields from one of the tables and converts the key field, then use that query in other queries instead of the table.

 

[TEOA]

Thanks for the quick Response ill give that a go at work tomorow

 

[pbaldy]

No problem, and welcome to the site!

 

[TEOA]

Worked great thanks