Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query text field for All or Part Matches 2

Status
Not open for further replies.

gwoman

Programmer
Nov 16, 2004
199
US
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
 
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!
 
thanks
would this work ...

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

=)
 
nope, that wouldn't work.

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

--------------------
Procrastinate Now!
 
That results in Access treating fldB as a parameter ...

=)
 
does it work then?

--------------------
Procrastinate Now!
 
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
 
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:
Code:
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.
 
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 [smile]

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
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
 
Added Table B Division Orders Table and it is still thorwing a promtp ...

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

--------------------
Procrastinate Now!
 
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
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top