SQL Criteria

[TKnight]

Hi I have a Criteria based on an IF statement where the TRUE outcome requires a sub query to be run and the FALSE outcome requires there to be no criteria in the field. The statement i've got is below...

IIf([Forms]![Search_Form_Titles]![Search_Form_Preferable_Skills]![NoOfSkills]>0,(SELECT [Candidate_Skills] FROM [Candidate_Query] WHERE [Candidate_Skills] Like"*"&[Search_Prefered_Skills].[Searched_Skill]&"*"),Like "*")

The SQL part works fine if [NoOfSkills] is >0 what doesn't work is the FALSE part of the IF statement Like "*"
It doesn't throw up an error message, it just doesn't return any records. If I take out the whole statement and just put in Like "*" then it works fine and returns all records. Can anyone help? Anything that would go in the FALSE part of the statement that would return all records will do fine, thanks, Tom.

 

[dcx693]

Usually, if you leave the criteria line blank in a query, it will return everything for that field. Have you tried leaving the false part of the IIf function blank?

 

[CJBIRKIN]

Hi


This should give you some ideas.

Basically you ask the query to equate the selection to true or false
If a name is entered then the answer = true the data is returned. If the answer is incorrect then a false is returned and no data. If the criteria is blank then all records are returned.

SELECT tbl_name.ID, tbl_name.Name
FROM tbl_name
WHERE (((IIf(IsNull([Please enter name]),True,[Name] Like [Please enter name]))<>False));


Hope that helps a bit

Chris