Access query does not display all expected results

[OrionElectrotech]

I am trying to run a query pulling data together from 2 tables - 1) a list of candidates, and 2) a list of their skills. So one candidate will have many skills.
The query runs from criteria specified in a form, and returns a list of suitable candidates into another (continuous)form.
All works fine for the first few searches, but then it stops working properly. It looks almost as though the results have been filtered, as only some of the candidates are appearing, even when no criteria are entered.
I am self taught on access, so it is no doubt something very basic I have not done right, but would appreciate any advice!
Thanks
Caroline

 

[dhookom]

Could you share your SQL view of the query?

Duane
Hook'D on Access
MS Access MVP

 

[OrionElectrotech]

This is it -

SELECT DISTINCT CANDIDATES.ca_Name, CANDIDATES.ca_Address, CANDIDATES.ca_Postcode, CANDIDATES.ca_Availability, CANDIDATES.[ca_Avail Notes], CANDIDATES.ca_Location, CANDIDATES.ca_LocationMore, CANDIDATES.ca_ContractType, [Skill-Candidate].skcand_skill, CANDIDATES.ca_Mobile, CANDIDATES.[ca_Work Tel No], CANDIDATES.[ca_Home Tel No], CANDIDATES.ca_EMail, CANDIDATES.ca_cvdetails
FROM CANDIDATES INNER JOIN [Skill-Candidate] ON CANDIDATES.ca_Name = [Skill-Candidate].skcand_candName
WHERE (((CANDIDATES.ca_Name) Like "*" & [Forms]![SEARCHCandidate]![ca_Name] & "*") AND ((CANDIDATES.ca_Address) Like "*" & [Forms]![SEARCHCandidate]![ca_address] & "*") AND ((CANDIDATES.ca_Postcode) Like "*" & [Forms]![SEARCHCandidate]![ca_postcode] & "*") AND ((CANDIDATES.ca_Location)=[Forms]![SEARCHCandidate]![ca_location]) AND ((CANDIDATES.ca_LocationMore) Like "*" & [Forms]![SEARCHCandidate]![ca_locationmore] & "*") AND ((CANDIDATES.ca_ContractType)=[Forms]![SEARCHCandidate]![ca_contracttype]) AND (([Skill-Candidate].skcand_skill)=[Forms]![SEARCHCandidate]![skcand_skill]) AND ((CANDIDATES.ca_cvdetails) Like "*" & [Forms]![SEARCHCandidate]![ca_cvdetails] & "*"));

Caroline

 

[dhookom]

Does every candidate have something in the ca_LocationMore field? Using Like "*" will only return records where the field is not null.

Duane
Hook'D on Access
MS Access MVP

 

[OrionElectrotech]

No, they won't always have an entry, but I have set all the fields in the table to Required "Yes" Allow Zero Length "Yes", so I think they should all have null values entered if left blank?
[As a general rule, If I go back and change the field properties in a table after setting various queries and forms to run from it, should I set the forms/queries up again?]


SELECT DISTINCT CANDIDATES.ca_name, CANDIDATES.ca_Address, CANDIDATES.ca_Postcode, CANDIDATES.ca_Availability, CANDIDATES.[ca_Avail Notes], CANDIDATES.ca_Location, CANDIDATES.ca_LocationMore, CANDIDATES.ca_ContractType, [Skill-Candidate].skcand_skill, CANDIDATES.ca_Mobile, CANDIDATES.[ca_Work Tel No], CANDIDATES.[ca_Home Tel No], CANDIDATES.ca_EMail, CANDIDATES.ca_cvdetails
FROM CANDIDATES INNER JOIN [Skill-Candidate] ON CANDIDATES.ca_name = [Skill-Candidate].skcand_candName
WHERE (((CANDIDATES.ca_name) Like "*" & [Forms]![SEARCHCandidate]![ca_name] & "*") AND ((CANDIDATES.ca_Address) Like "*" & [Forms]![SEARCHCandidate]![ca_address] & "*") AND ((CANDIDATES.ca_Postcode) Like "*" & [Forms]![SEARCHCandidate]![ca_postcode] & "*") AND ((CANDIDATES.ca_Location)=[Forms]![SEARCHCandidate]![ca_location]) AND ((CANDIDATES.ca_LocationMore) Like "*" & [Forms]![SEARCHCandidate]![ca_locationmore] & "*") AND ((CANDIDATES.ca_ContractType)=[Forms]![SEARCHCandidate]![ca_contractType]) AND (([Skill-Candidate].skcand_skill)=[Forms]![SEARCHCandidate]![skcand_skill]) AND ((CANDIDATES.ca_cvdetails) Like "*" & [Forms]![SEARCHCandidate]![ca_cvdetails] & "*"));

 

[PHV]

You may try this:
SELECT DISTINCT C.ca_name, C.ca_Address, C.ca_Postcode, C.ca_Availability, C.[ca_Avail Notes], C.ca_Location, C.ca_LocationMore
, C.ca_ContractType, S.skcand_skill, C.ca_Mobile, C.[ca_Work Tel No], C.[ca_Home Tel No], C.ca_EMail, C.ca_cvdetails
FROM CANDIDATES AS C INNER JOIN [Skill-Candidate] AS S ON C.ca_name = S.skcand_candName
WHERE Nz(C.ca_name,"") Like "*" & [Forms]![SEARCHCandidate]![ca_name] & "*"
AND Nz(C.ca_Address,"") Like "*" & [Forms]![SEARCHCandidate]![ca_address] & "*"
AND Nz(C.ca_Postcode,"") Like "*" & [Forms]![SEARCHCandidate]![ca_postcode] & "*"
AND (C.ca_Location=[Forms]![SEARCHCandidate]![ca_location] OR [Forms]![SEARCHCandidate]![ca_location] Is Null)
AND Nz(C.ca_LocationMore,"") Like "*" & [Forms]![SEARCHCandidate]![ca_locationmore] & "*"
AND (C.ca_ContractType=[Forms]![SEARCHCandidate]![ca_contractType] OR [Forms]![SEARCHCandidate]![ca_contractType] Is Null)
AND (S.skcand_skill=[Forms]![SEARCHCandidate]![skcand_skill] OR [Forms]![SEARCHCandidate]![skcand_skill] Is Null)
AND Nz(C.ca_cvdetails,"") Like "*" & [Forms]![SEARCHCandidate]![ca_cvdetails] & "*";

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Required "Yes" Allow Zero Length "Yes", so I think they should all have null values entered if left blank

I don't ever do this since some field won't have values at least at some point in their life. A Null value is not the same as a zero-length-string.

I use a variation of PH's solution. Where PH had:

AND Nz(C.ca_LocationMore,"") Like "*" & [Forms]![SEARCHCandidate]![ca_locationmore] & "*"

I generally use:

AND C.ca_LocationMore & "" Like "*" & [Forms]![SEARCHCandidate]![ca_locationmore] & "*"

If the query is the Record Source for a form or report, I would not have any of these dynamic criteria saved in the query.

Duane
Hook'D on Access
MS Access MVP

 

[OrionElectrotech]

Thank you both for your replies. I shall try them, and let you know.
Being a novice at this, just one question - "Nz" is what exactly??

Caroline

 

[Golom]

NZ Function

 

[OrionElectrotech]

It works!!

Thanks PHV, I tried your suggestion and it seems to be working well (although is it right that when I look back at the SQL view of my query now it is twice as long as what you originally wrote?)

Thank you all for your help, I may well be back soon if they need something changed! In the mean time I shall try to learn more about these queries, and VB (fat chance, I'm confused already)

Caroline