GrandMasterTuck
In need of medication
- Local time
- Today, 12:57
- Joined
- May 4, 2013
- Messages
- 129
Hi again. I had an issue with writing LIKE statements in query criteria yesterday, and a couple folks stepped in to give me a hand:
http://www.access-programmers.co.uk/forums/showthread.php?t=276166
The answer they gave worked perfectly when I only used a single table in the query. But as soon as I did an INNER JOIN with two other tables, now I get parameter value prompts when I open frmSearch, and instead of seeing ALL my records when the controls are left null, I get only the first record in the table.
Here's the SQL of the query, can you point out what I messed up? NOTE that this SQL was 'written' by Access.. as I used the Query builder to set all the 'Like or Is Null' statements, then clicked SQL and sorta formatted the code so I can see what I'm looking at (instead of superthick wall-o-code):
Basically, this is a searchable database of participants in a pet-adoption program, along with the participants' pets history and address history (hence the linked tables as opposed to additional columns in one single table for pets and addresses... there are more than one in some cases). The frmSearch allows a person to run quick searches based upon ANY item in the database, such as name, pets, addresses, pet age, pet type, county of residence, etc. I need to be able to pick ANY field on frmSearch and type a value, and have the qrySearch return records for ANY record's related column wherein any part of it matches what I typed.
The statements as written worked PERFECTLY right up until I added the INNER JOIN. Now I get a set of parameter value prompts for every field on frmSearch that's referenced in the SQL for EACH table I linked to tblPeople, and if I leave everything null and click Search, I want to see EVERY person, but I'm only seeing the very FIRST person in tblPeople.
Thanks again!
http://www.access-programmers.co.uk/forums/showthread.php?t=276166
The answer they gave worked perfectly when I only used a single table in the query. But as soon as I did an INNER JOIN with two other tables, now I get parameter value prompts when I open frmSearch, and instead of seeing ALL my records when the controls are left null, I get only the first record in the table.
Here's the SQL of the query, can you point out what I messed up? NOTE that this SQL was 'written' by Access.. as I used the Query builder to set all the 'Like or Is Null' statements, then clicked SQL and sorta formatted the code so I can see what I'm looking at (instead of superthick wall-o-code):
Code:
SELECT tblPeople.name, tblPeople.num
FROM (tblPeople INNER JOIN tblAddresses ON tblPeople.name = tblAddresses.name)
INNER JOIN tblPets ON tblPeople.name = tblPets.name
WHERE (
((tblPeople.PersonInmateID) Like "*" & [Forms]![frmSearch].[sname] & "*"
Or [Forms]![frmSearch].[sname] Is Null)
AND ((tblPeople.name) Like "*" & [Forms]![frmSearch].[snum] & "*"
Or [Forms]![frmSearch].[snum] Is Null)
AND ((tblPets.type) Like "*" & [Forms]![frmSearch].[stype] & "*"
Or [Forms]![frmSearch].[stype] Is Null)
AND ((tblPets.age) Like "*" & [Forms]![frmSearch].[sage] & "*"
Or [Forms]![frmSearch].[sage] Is Null)
AND ((tblAddresses.Address) Like "*" & [Forms]![frmSearch].[saddy] & "*"
Or [Forms]![frmSearch].[saddy] Is Null)
AND ((tblAddresses.County) Like "*" & [Forms]![frmSearch].[scounty] & "*"
Or [Forms]![frmSearch].[scounty] Is Null));
Basically, this is a searchable database of participants in a pet-adoption program, along with the participants' pets history and address history (hence the linked tables as opposed to additional columns in one single table for pets and addresses... there are more than one in some cases). The frmSearch allows a person to run quick searches based upon ANY item in the database, such as name, pets, addresses, pet age, pet type, county of residence, etc. I need to be able to pick ANY field on frmSearch and type a value, and have the qrySearch return records for ANY record's related column wherein any part of it matches what I typed.
The statements as written worked PERFECTLY right up until I added the INNER JOIN. Now I get a set of parameter value prompts for every field on frmSearch that's referenced in the SQL for EACH table I linked to tblPeople, and if I leave everything null and click Search, I want to see EVERY person, but I'm only seeing the very FIRST person in tblPeople.
Thanks again!