Search Query - Retrieving records with null fields

[kevins]

I have a search form that works great, but only if every field is populated. Basically, I can search for one or all of the parameters and it will retrieve records that meet those parameters. It will not, however retrieve a record if any of those fields are null. Is there a fix so that it will include records with null fields? Below is my code:

SELECT [Candidates Extended].[Contact Name], [Candidates Extended].Company, [Candidates Extended].[Job Title], [Candidates Extended].City, [Candidates Extended].State, [Candidates Extended].Status, [Candidates Extended].[Specialty 1], [Candidates Extended].[Specialty 2], [Candidates Extended].[Specialty 3], [Candidates Extended].[Specialty 4], [Candidates Extended].Category
FROM [Candidates Extended]


WHERE ((([Candidates Extended].[Contact Name]) Like "*" & [Forms]![Search Form]![Name] & "*") AND (([Candidates Extended].Company) Like "*" & [Forms]![Search Form]![Company] & "*") AND (([Candidates Extended].[Job Title]) Like "*" & [Forms]![Search Form]![Job Title] & "*") AND (([Candidates Extended].City) Like "*" & [Forms]![Search Form]![City] & "*") AND (([Candidates Extended].State) Like "*" & [Forms]![Search Form]![State] & "*") AND (([Candidates Extended].Status) Like "*" & [Forms]![Search Form]![Status] & "*") AND (([Candidates Extended].[Specialty 1]) Like "*" & [Forms]![Search Form]![Specialty] & "*") AND (([Candidates Extended].[Specialty 2]) Like "*" & [Forms]![Search Form]![Specialty] & "*") AND (([Candidates Extended].[Specialty 3]) Like "*" & [Forms]![Search Form]![Specialty] & "*") AND (([Candidates Extended].[Specialty 4]) Like "*" & [Forms]![Search Form]![Specialty] & "*") AND (([Candidates Extended].Category) Like "*" & [Forms]![Search Form]![Category] & "*"));

Thanks!

[aytee111]

(([Candidates Extended].[Contact Name]) Like "*" & [Forms]![Search Form]![Name] & "*") Or IsNull([Forms]![Search Form]![Name])) And ...

I don't know if this would work or not. I was hoping someone else would answer!

Make a copy of this query - remove all the criteria - do the above, see if it works.
If it works, add the next field, check your results. Etc
It is hard to troubleshoot complex queries/issues.

Otherwise, do you know VBA? You can make a string.

[kevins]

No dice...I'm an intermediate Access user at best, so I'm not familiar with VBA. I'm thinking of getting a book and trying to teach myself some tricks for Access and VBA. Any recommendations?

[orange]

Try changing your ANDs to ORs and see what you get.

[orange]

Try changing your ANDs to ORs and see if that works.

see post #12 here for similar situation
https://www.accessforums.net/queries...ues-20931.html

[kevins]

The AND operator isn't what is causing the problem. The problem is that the query is only returning the records that have entries fully completed, but not those that have null entries. For example, I can type in "Dave" and the query will return only those records with the first name Dave, but only if the city, state, category fields, etc. are populated. If I don't have a category for one of the Daves, it won't retrieve that record.

[tommy93]

Im having the exact same issue with my query. I have a thread going about that aswell...

I have tried both adding the wildcard &"*" and "Or IsNull" to the criteria but still no luck. Im still trying to work around it though

[Kenny]

Hi, I have a question I think very similar to this. I have a query that I use with an "Unbound Form". There is approximately 10 fields on this form. I want user to have the ability to search using this unbound form. Some of the field’s don’t need to be searched all the time. How can I write the query so that when a user searches some of the fields and leaves some blank it, will still get the records that they need. In the query design menu I have this code in one of the fields: [forms]![frm Query transparts]![part] what if the user wants to leave this field blank when he is doing a search but he puts data in the others. I dont know VBA..Sorry.