Search for Partial Field in Subquery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I think I'm got Subqueries (semi-) figured out...

except for one I'm trying to create now. I want to search for owners of
properties. The owners are in a subform on the main property form. It
works, however I would like the search to run on partial results. i.e. if I
search for 'James' I want it to bring up 'James' as well as 'James B' etc.

This is my code (it is also searching for a few other things) right now it
just brings up 'James' (for example):

SELECT *
FROM Inventory
WHERE ProvincialID in
(SELECT ProvincialID
FROM Owners
WHERE Owners.FirstName =
[Forms]![NameOccupationSearch]![Name]
OR
Owners.LastName =
[Forms]![NameOccupationSearch]![Name]
OR
Owners.Occupation =
[Forms]![NameOccupationSearch]![Name]);


I have also tried adding this in certain areas, but no luck:

Like "*" & [Forms]![NameOccupationSearch]![Name] & "*"
 
cprav said:
[snip] I want to search for owners of
properties. The owners are in a subform on the main property form. It
works, however I would like the search to run on partial results. i.e. if I
search for 'James' I want it to bring up 'James' as well as 'James B' etc.

This is my code (it is also searching for a few other things) right now it
just brings up 'James' (for example):

SELECT *
FROM Inventory
WHERE ProvincialID in
(SELECT ProvincialID
FROM Owners
WHERE Owners.FirstName =
[Forms]![NameOccupationSearch]![Name]
OR
Owners.LastName =
[Forms]![NameOccupationSearch]![Name]
OR
Owners.Occupation =
[Forms]![NameOccupationSearch]![Name]);


I have also tried adding this in certain areas, but no luck:

Like "*" & [Forms]![NameOccupationSearch]![Name] & "*"
Click to expand...

I don't see why that wouldn't work, except possible for the
initial *

Try this:

.. . .
WHERE Owners.FirstName Like
[Forms]![NameOccupationSearch]![Name] & "*"
OR Owners.LastName Like
[Forms]![NameOccupationSearch]![Name] & "*"
OR Owners.Occupation Like
[Forms]![NameOccupationSearch]![Name] & "*"

I don't think it's causing trouble in this particular
situation, but you will eventually run into problems using a
reserved word such as Name.
 
Back
Top