Discussion Locked

Finding the Contents of One Field in Ano

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have been trying to find a way to compare a text field FieldAA in Table A to see if the data in text field FieldBB in Table B contains the string in FieldAA.

I am searching FieldBB for the value of each occurence of FieldAA. This value will change from record to record and I already know that the data in the two fields will seldom, if ever, be identical so the usual FieldAA = FieldBB statement will result in failure.

I have been trying to use the "Like" statement with the "*"wildcard,but have not been able to figure out a way to make either one work in a statement that includes a fieldname rather than a literal value.

Any ideas on if and how this can be done would be most appreciated.

7 replies

by shadowtraitor

If you use the select statement and
SELECT * FROM PARTS WHERE FieldAAvalue LIKE '%FieldBBvalue%';

the wild card value is the '%' sign aND YOU USE THE Field names as paramaters that will take the place of Location and Back in the Statement.
that should help you.

by fofa

Like is designed to work with a string/literal, that was just the design. You can use a filed, but you can't use wildcards along with it. And the access wildcard is * not % like other SQL engines. You most likely will have to breakup the table and use multi-queris or what I usually do is write some VBA to do it. I woulod write a VBA to create a SQL statement that would process each one, then put the results in a new table with unique keys pointing back to the other tables. Than you can use a regular query to view the results.

by fred_e944

Check out the behaviour of the InStr operator in Access VBA. It may be of some help.

This discussion is closed to new replies.