Syntax for Joining on a range 1

[awesomeBA]

I would like to create a join using a range. Do I need to indicate that the match will be a range of the field in the second table?

 

[jebry]

Hi!

Just do a standard join and limit the field from the second table in the Where clause.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[awesomeBA]

Limit it how?

 

[PHV]

Something like this ?
ON A.SomeFiled Between B.Field1 And B.Field2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[awesomeBA]

The fields that i am trying to join on are both serial number fields.
T1 contains the entire number t2 contains 5 digits of the serial number.

 

[jebry]

Hi!

You can do this:

table1 Inner Join table2 On Left(table1.T1, 5) = table2.t2

If the fields are number fields then you can wrap table1.T1 and table2.t2 in a Format function.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[awesomeBA]

Both are number field s but no calcs are necessary just a match. Does the format function give me value in this area?

 

[jebry]

Hi!

The format function will just change the number to a string so that the Left function will work on it. Also, I made the assumption that t2 contained the first 5 digits from T1. If that isn't the case then let us know and we can get you the appropriate function to match what you want. One alternative would be:

table1 Inner Join table2 On InStr(Format(table1.T1), Format(table2.t2)) <> 0

This will match if t2 is anywhere in T1. But that may get you matches that you don't want. It just depends on exactly how the two fields are related.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[awesomeBA]

I think the second query will give me what I need. Have a star!!!

Tks!