Criteria for selecting records

[Joe Ventre]

I am trying to write a query where all records where the value of a field in
one table is contained within the value of a field in another table. For
example If Field A in Table Z has the value XXX and Field B in Table Y has
the value aaaXXXzzz I would want data from both of those records to be shown
in the results. Once I place these fields in the query design form how do I
write the statement that goes into the Criteria line of the form. If I put
[Z]![A] on the criteria line under Field B on the form I can find exact
matches but if I use Like *[Z]![A]* (to designate Like with wildcard
characters in front of and behind the field name) the query does not work.

 

[Marshall Barton]

I am trying to write a query where all records where the value of a field in
one table is contained within the value of a field in another table. For
example If Field A in Table Z has the value XXX and Field B in Table Y has
the value aaaXXXzzz I would want data from both of those records to be shown
in the results. Once I place these fields in the query design form how do I
write the statement that goes into the Criteria line of the form. If I put
[Z]![A] on the criteria line under Field B on the form I can find exact
matches but if I use Like *[Z]![A]* (to designate Like with wildcard
characters in front of and behind the field name) the query does not work.

If I understand what you want, I don't think you can write
this query in the query design grid, you need to use SQL
view:

SELECT TableZ.*, TableY.*
FROM TableZ INNER JOIN TableY
ON TableY.FieldB LIKE "*" & TableZ.FieldA & "*"

 

[Joe Ventre]

Marshall,

Thanks for the help. The query worked with one small difference that I
don't yet understand. In the ON part of your query I had to reverse the
placement of the two tables/fields. In other words it worked if the * were
put around the filed that has the value I want, not around the field that
contains that value plus other characters (hope that makes some sense).

I am trying to write a query where all records where the value of a field
in
one table is contained within the value of a field in another table. For
example If Field A in Table Z has the value XXX and Field B in Table Y
has
the value aaaXXXzzz I would want data from both of those records to be
shown
in the results. Once I place these fields in the query design form how do
I
write the statement that goes into the Criteria line of the form. If I
put
[Z]![A] on the criteria line under Field B on the form I can find exact
matches but if I use Like *[Z]![A]* (to designate Like with wildcard
characters in front of and behind the field name) the query does not work.

If I understand what you want, I don't think you can write
this query in the query design grid, you need to use SQL
view:

SELECT TableZ.*, TableY.*
FROM TableZ INNER JOIN TableY
ON TableY.FieldB LIKE "*" & TableZ.FieldA & "*"

 

[Marshall Barton]

The string to search goes on the left side of Like and the
wildcard pattern goes on the right.

I'm pretty sure I had the tables/fields as you specified in
you original post, but that's one of the problems with
working on an abstracted problem description. One or the
other of us can get the abstract names mixed up. As long as
you got it working, it doesn't really matter which of us is
confused.
--
Marsh
MVP [MS Access]

Thanks for the help. The query worked with one small difference that I
don't yet understand. In the ON part of your query I had to reverse the
placement of the two tables/fields. In other words it worked if the * were
put around the filed that has the value I want, not around the field that
contains that value plus other characters (hope that makes some sense).

I am trying to write a query where all records where the value of a field
in
one table is contained within the value of a field in another table. For
example If Field A in Table Z has the value XXX and Field B in Table Y
has
the value aaaXXXzzz I would want data from both of those records to be
shown
in the results. Once I place these fields in the query design form how do
I
write the statement that goes into the Criteria line of the form. If I
put
[Z]![A] on the criteria line under Field B on the form I can find exact
matches but if I use Like *[Z]![A]* (to designate Like with wildcard
characters in front of and behind the field name) the query does not work.

"Marshall Barton"wrote
If I understand what you want, I don't think you can write
this query in the query design grid, you need to use SQL
view:

SELECT TableZ.*, TableY.*
FROM TableZ INNER JOIN TableY
ON TableY.FieldB LIKE "*" & TableZ.FieldA & "*"