Query does not return records with blank field(s) 2

[olympus]

My queries are not showing any records that contain one or more blank fields.

I have a table (named DrugInfo) with 35 fields. I want to find records based upon user input for up to five of these fields. These fields are: drug, rph, requester, question summary, question response.

I created a form (named search) with text boxes for these 5 fields to allow the user to input a value into one or more of these fields. The default value for these fields is "". On this form, under properties, I have named the text boxes: qdrug1, qrph1, qrequester1, qqeustion1, qresponse1.

In a query (named Select) on my DrugInfo table,I want to take the one or more criteria that were entered on the form and return all records that match. The problem is that of the 35 fields in the database, there can be one or more that have been left blank. This results in an incomplete display of data. The criteria for the first field(drug) is:

Like nz([Forms]![search].[qdrug1] & "*")

the other 4 criteria follow the format of: Like Nz("*" & [Forms]![search].[qxxx1] & "*")

I have tried to play around with the NZ command, but I cannot get it to return all records that match the requested text and may have one or more of the fields left blank.

I have not used Access very much so please pardon my question on what is probably an rudimentary task, but I have spent hours on this without success. Thanks.

John

 

[dhookom]

Your field values might be Null which will not match any "Like" expression. You may need to change the WHERE clause to something like:

WHERE [FieldA] & "" Like [Forms]![search].[qdrug1] & "*"

Duane
Hook'D on Access
MS Access MVP

 

[olympus]

Duane, I tried adding this expression fron of my existing query and now the search yields zero records. I'm just guessing here but could an IIF statement work? I know that some of my fields are indeed "null", but have not been able to work around this. I thought that the "nz" function would convert the null fields to zero. Unfortunately, I could not get that to work either. Is there a way to enter multiple filter parameters via a form? I think the filter by form function would work if I could have all of the necessary wild card funtions inserted automatically and not deleted by the user's input into the textbox. Thanks.
John

 

[dhookom]

Please post the SQL of your query.

I never use Nz() with only one argument. IMO, always provide the value to return if the value is null.

Duane
Hook'D on Access
MS Access MVP

 

[olympus]

Here's the SQL. I did not write this directly, I used the criteria fileds on the query design and this is the SQL result. Many Thanks.
John

SELECT [Drug Info Database].Drug, [Drug Info Database].RPh, [Drug Info Database].Requester, [Drug Info Database].[Question summary], [Drug Info Database].[Response summary], [Drug Info Database].[Response summary], [Drug Info Database].[Follow up], [Drug Info Database].[Clinical Pharmacology], [Drug Info Database].IDIS, [Drug Info Database].Micromedex, [Drug Info Database].[PubMed/Medline], [Drug Info Database].[Phoned Manufacturer], [Drug Info Database].Textbook, [Drug Info Database].Google, [Drug Info Database].LexiOnline, [Drug Info Database].website, [Drug Info Database].[Package Insert], [Drug Info Database].[Facts and comparisons], [Drug Info Database].Trissels, [Drug Info Database].Other, [Drug Info Database].[Other 2], [Drug Info Database].[Article 1], [Drug Info Database].[Article 2], [Drug Info Database].[Article 3], [Drug Info Database].[Article 4], [Drug Info Database].Misc, [Drug Info Database].[Misc 2]
FROM [Drug Info Database]
WHERE ((([Drug Info Database].Drug) Like Nz([Forms]![search].[qdrug1] & "*")) AND (([Drug Info Database].RPh) Like Nz("*" & [Forms]![search].[qrph1] & "*")) AND (([Drug Info Database].Requester) Like Nz("*" & [Forms]![search].[qrequester1] & "*")) AND (([Drug Info Database].[Question summary]) Like Nz("*" & [Forms]![search].[qquestion1] & "*")) AND (([Drug Info Database].[Response summary]) Like Nz("*" & [Forms]![search].[qresponse1] & "*")));

 

[dhookom]

How about trying my suggestion which might look like:

SELECT Drug, RPh, Requester, [Question summary], [Response summary], 
[Response summary], [Follow up], [Clinical Pharmacology], IDIS, 
Micromedex, [PubMed/Medline], [Phoned Manufacturer], Textbook, 
Google, LexiOnline, website, [Package Insert], [Facts and comparisons], 
Trissels, Other, [Other 2], [Article 1], [Article 2], [Article 3], 
[Article 4], Misc, [Misc 2]
FROM [Drug Info Database]
WHERE Drug & "" Like [Forms]![search].[qdrug1] & "*" AND 
 RPh & "" Like "*" & [Forms]![search].[qrph1] & "*" AND 
 Requester & "" Like "*" & [Forms]![search].[qrequester1] & "*" AND 
 [Question summary] & "" Like "*" & [Forms]![search].[qquestion1] & "*" AND
 [Response summary] & "" Like "*" & [Forms]![search].[qresponse1] & "*";

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

WHERE Nz([Drug Info Database].Drug,"") Like [Forms]![search].[qdrug1] & "*"
AND Nz([Drug Info Database].RPh,"") Like "*" & [Forms]![search].[qrph1] & "*"
AND ...

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

 

[olympus]

Duane and PH, Thank you so much! Your suggestions solved the problem.
John