Query Criteria - Substring

  • Thread starter Thread starter Jim Miller
  • Start date Start date
J

Jim Miller

I'm trying to put together a query, comparing fields in
two tables, and returning the result where the field in
the first table is a substring of the field in the second
table.

It seems so easy to do with criteria in a query, such as:

Like "*boxes*"

Which gives me back all the records where "boxes" is a
substring of the field I've specified.

But if I try criteria something like:

Like "*[table2]![products]*"

Access just laughs at me.

Thanks for your help.
 
Try something along the lines of

SELECT Table2.requiredColumn
FROM Table2 INNER JOIN Table1 ON Table2.requiredColumn LIKE
Table1.compareColumn & "*"

You will have to change the table and column names to suit
your app.

Hope This Helps
Gerald Stanley MCSD
 
Gerald, thanks a lot :)

I added an extra "*" in front of the
Table2.requiredColumn .

It works great, man. Much appreciated.

-----Original Message-----
Try something along the lines of

SELECT Table2.requiredColumn
FROM Table2 INNER JOIN Table1 ON Table2.requiredColumn LIKE
Table1.compareColumn & "*"

You will have to change the table and column names to suit
your app.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I'm trying to put together a query, comparing fields in
two tables, and returning the result where the field in
the first table is a substring of the field in the second
table.

It seems so easy to do with criteria in a query, such as:

Like "*boxes*"

Which gives me back all the records where "boxes" is a
substring of the field I've specified.

But if I try criteria something like:

Like "*[table2]![products]*"

Access just laughs at me.

Thanks for your help.
.
Click to expand...
.
Click to expand...
 
Is there a way to accomplish the same thing with a parameter Query?

Gerald Stanley said:
Try something along the lines of

SELECT Table2.requiredColumn
FROM Table2 INNER JOIN Table1 ON Table2.requiredColumn LIKE
Table1.compareColumn & "*"

You will have to change the table and column names to suit
your app.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I'm trying to put together a query, comparing fields in
two tables, and returning the result where the field in
the first table is a substring of the field in the second
table.

It seems so easy to do with criteria in a query, such as:

Like "*boxes*"

Which gives me back all the records where "boxes" is a
substring of the field I've specified.

But if I try criteria something like:

Like "*[table2]![products]*"

Access just laughs at me.

Thanks for your help.
.
Click to expand...
Click to expand...
 
Back
Top