I have a query that I'm trying to make. I two tables that I need to connect. One of the tables has a field with a 4 digit number (Table1), while the other table has a field with a 5 digit number (Table2). These two I need to join. I made a query with all of the original data from the latter table(Table2), except I did a right() function on the field to make it 4 digits.

So I have now have a query and a table. I want to join them in a new query so I can select a key from Table2 and then it will select all of the records from Table1 where these two fields are in common.

Now, my problem is that when I try this, with the join properties in either direction, I get a full resultset, only the fields from the other table are blank.

I hope I was clear enough.

a speedy response would be great. Thank you

bauer

I experimented with your problem using Northwind's OrderDetails table (since the OrderID field provides a pre-made source of five-digit numbers).

Rather than using the right() function, try the following, replacing OrderID with your 5-digit field name.

x: [OrderID]-10000*Int([OrderID]/10000)

This returns a true number that you can use to connect to your four-digit table.