Matching the first 3 letters of the field

[nofear]

I have 2 tables with 1 field each
the filed of the fist table has a 3 digit number stored as text
and the field of the second table has a 6 digit number stored as text

I'd like to select each 6 digit number where the first 3 digits match
the field in the first table

I'm not sure If I can use the "LIKE"

[Allen Browne]

You can use LIKE in the FROM clause, like this:

SELECT Table1.*
FROM Table2 INNER JOIN Table1
ON Table2.[SomeField] Like Table1.[SomeField] & "*"

Access will not be able to display this query graphically (i.e. it's SQL
View only), and results will be read-only.

An alternative approach (probably more efficient) would be to create a query
that uses integer division to lose the least-significant 3 digits from table
2:
SELECT [SomeField] \ 1000 AS MyAlias
FROM Table2;

You could then use that query as in input table, and join it to Table1. This
will display graphically.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"nofear" <eu*******@hotmail.comwrote in message
news:66**********************************@k37g2000 hsf.googlegroups.com...

>I have 2 tables with 1 field each
the filed of the fist table has a 3 digit number stored as text
and the field of the second table has a 6 digit number stored as text

I'd like to select each 6 digit number where the first 3 digits match
the field in the first table

I'm not sure If I can use the "LIKE"

[nofear]

Thx
On Jul 2, 11:20*am, "Allen Browne" <[e-mail]:

You can use LIKE in the FROM clause, like this:

SELECT Table1.*
FROM Table2 INNER JOIN Table1
* * ON Table2.[SomeField] Like Table1.[SomeField] & "*"

Access will not be able to display this query graphically (i.e. it's SQL
View only), and results will be read-only.

An alternative approach (probably more efficient) would be to create a query
that uses integer division to lose the least-significant 3 digits from table
2:
* * SELECT [SomeField] \ 1000 AS MyAlias
* * FROM Table2;

You could then use that query as in input table, and join it to Table1. This
will display graphically.

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"nofear" <[e-mail] in message

news:66**********************************@k37g2000 hsf.googlegroups.com...

I have 2 tables with 1 field each
the filed of the fist table has a 3 digit number stored as text
and the field of the second table has a 6 digit number stored as text

I'd like to select each 6 digit number where the first 3 digits match
the field in the first table

I'm not sure If I can use the "LIKE"