Query text field for All or Part Matches 2

[gwoman]

I have 2 tables in Access ... I need to use TableA.FieldA to test against TableB.FieldB to see if there are any matches. However ... TableA.FieldA is first and last names and TableB.FieldB is a text field containing full names (first and last, first last & middle initial, and possibly more than one name) and or business names.

Example: TableA.FieldA = John Smith ... I want to search TableB.FieldB to see if "John Smith" is contained in all or part ... of the text field.

... I tried using "IN" and a wildcard ... but it is not wokring.

Any suggestions would be much appreciated ...

Thanks so much!

gwoman

 

[Crowley16]

where "John Smith" = "*John Smith*"

p.s. the IN keyword cannot be use in conjunction with the * wildcard character, as far as I know.

--------------------
Procrastinate Now!

 

[gwoman]

thanks
would this work ...

where TableA.FieldA = "*TableB.FieldB*"

=)

 

[Crowley16]

nope, that wouldn't work.

try where fldA LIKE "*" & fldB & "*"

--------------------
Procrastinate Now!

 

[gwoman]

That results in Access treating fldB as a parameter ...

=)

 

[Crowley16]

does it work then?

--------------------
Procrastinate Now!

 

[gwoman]

No ... it throws a prompt ...
This is what I currently tried ... and it does the same thing ....
WHERE People_Active.PRSN_NME Like "%[Division Orders]![OWNER_NAME]%"

I am still searching ... but I haven't found where you can use a field as the criteria string ... it is wanting a specific value for the "like" keyword to search for ....

Thanks!
=)
gwoman

 

[lespaul]

what's the rest of the SQL?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[HarleyQuinn]

I tried Crowley's answer from 10:31 and it worked fine for me, in the criteria for (what would be TableB.FieldB) I placed:

Like "*" & [Table1]![b] & "*"

It produced the expected results.

Could you post the SQL you used to test this when it asked you for the parameter?

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

I'm going to bet on the fact that when you were prompted for a parameter you didn't have TableB added as a table in the query, if you add the table it should work fine

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gwoman]

Here it is ....

SELECT People_Active.Emp_ID, People_Active.SSN_ID, People_Active.PRSN_NME, People_Active.PRSN_TYPE, People_Active.ACTV_INDC, People_Active.WRKR_SVC_PRD_TERM_DTTM
FROM People_Active
WHERE (((Aera_People_Active.PRSN_LST_NME) Like "*" & [Division Orders]![OWNER_NAME] & "*"));

I am getting a prompt for Division Orders!OWNER_NAME ...

Thanks
gwoman

 

[gwoman]

Added Table B Division Orders Table and it is still thorwing a promtp ...

=)
gwoman

 

[Crowley16]

the syntax is fine, check your field names and table names and make sure that all tables are specified

--------------------
Procrastinate Now!

 

[gwoman]

Checked the table names ... here is the latest ...

SELECT People_Active.Emp_ID, People_Active.SSN_ID, People_Active.PRSN_NME, People_Active.PRSN_TYPE, People_Active.ACTV_INDC, People_Active.WRKR_SVC_PRD_TERM_DTTM
FROM People_Active, [Division Orders]
WHERE (((People_Active.PRSN_NME) Like "*" & [Division Orders]![OWNER_NAME] & "*"));

I am still getting a promtp for [Division Orders]![OWNER_NAME]...

When I change the "*"& to "% ... I do not get a prompt ... the query runs ... but no data is returned ... which is not correct as I now there are matches ...


SELECT People_Active.Emp_ID, People_Active.SSN_ID, People_Active.PRSN_NME, People_Active.PRSN_TYPE, People_Active.ACTV_INDC, People_Active.WRKR_SVC_PRD_TERM_DTTM
FROM People_Active, [Division Orders]
WHERE (((People_Active.PRSN_NME) Like "% [Division Orders]![OWNER_NAME] %"));

I really appreciate your help ... thanks!

=)
gwoman

 

[Crowley16]

eh, the 2nd query is testing for a static string value, not value inside a field.

now that you've got a cross join between the 2 tables, use [Division Orders].Owner_Name

--------------------
Procrastinate Now!

 

[gwoman]

Thanks ... it's working!
=)
gwoman