Parameter Value Prompts in Query using "Like Or Is Null" Criteria (1 Viewer)

[GrandMasterTuck]

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):

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!

 

[RainLover]

For a Start change the word NAME to something like MyNAME.

Name is a Reserved word and should not be used. It could return the Name of the Form instead of what you want.

Do a Google and get yourself a full list of reserved word for your future reference.

I can't see where your problem lies. it would be good to have a cut down version of your database with a small amount of dummy data. Just for peoples privacy.

Failing that a Pic of your Relationships may help.

 

[SmallTime]

Just and idea since you're using reserved words.

Would it be that you need to explicitly declare the variables. It's the case in queries that don't have definite headers such a cross-tab queries but may also be the case here. Just a shot in the dark but try setting the variables in the parameter of the query and then give it whirl.

 

[Galaxiom]

What happens if you use LEFT OUTER JOIN instead of INNER JOIN ?

 

[RainLover]

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.

After a review of your problem, it would appear that your loins are wrong. There is not enough information to give a qualified answer.

Can you post a cut down version of this data base. Leave out things that are not part of the problem.

 

[GrandMasterTuck]

I am so sorry, you guys, but I'm afraid I've wasted your valuable time on a stupid rookie mistake. The problem, in case you're curious, was this part:

[Forms]![frmSearch].[sname]

See, I named all my forms with underscores, like this: [frm_Seach], and when I wrote the query the first time around (with one table only), I wrote it correctly, but when I went back and added the other linked tables, I incorrectly called them without the underscore, and that's why the Parameter Value prompts came up. That's why the single-table version worked, but the multi-table version didn't. Stupid, I know.

Anyway, it's working great now, so I've left thanks for all of you who took the time to answer me. Thanks again!

 

[RainLover]

I have made worse mistakes, so don't give it a second thought.

They say that a man who has never made a mistake has never made any thing.

 

[SmallTime]

Easily done my friend, but at least your loins are OK

 

[RainLover]

The Loins could be much better.

At least we now have the Gold Coast to barrack

I love watching Ablett when he is in form.