Searchable report that accepts blank fields

[jmist]

Hey all,
I have a searchable report/form in access that uses an intersection query to populate results for certain parameters. I've included a screenshot of my report below.

This report is linked to a form which is linked to an intersection query. Currently, in order to run the query, I need to fill in all fields of the report. However, I would like to be able to run the query while leaving some of the fields blank ( I.e dates or consult types). I've tried using an 'IS NULL() operator for my sql code but this hasn't seemed to work. My sql code for the query is pasted below:

SELECT C1.Consult_ID, C1.Consult_No, C1.Consult_Type, C1.Intake_Date, C1.[Adult-Peds], C1.Nutshell, C1.Consult_Summary
FROM Consults AS C1 LEFT JOIN Consult_Reasons AS C2 ON C1.Consult_ID = C2.Consult_ID
WHERE (IsNull([Forms]![FrmCriteria]![ConsultType]) OR C1.Consult_Type=[Forms]![FrmCriteria]![ConsultType])
AND (IsNull([Forms]![FrmCriteria]![StartDate]) OR C1.Intake_Date >= [Forms]![FrmCriteria]![StartDate])
AND (IsNull([Forms]![FrmCriteria]![EndDate]) OR [Forms]![FrmCriteria]![EndDate] <= [Forms]![FrmCriteria]![EndDate])
AND (IsNull([Forms]![FrmCriteria]![AgeAggregate]) OR C1.[Adult-Peds]=[Forms]![FrmCriteria]![AgeAggregate])
AND (IsNull([Forms]![FrmCriteria]![Keyword]) OR C1.Nutshell Like "" & [Forms]![FrmCriteria]![Keyword] & "")
AND (IsNull([Forms]![FrmCriteria]![Reason]) OR C2.ReasonID=[Forms]![FrmCriteria]![Reason])
AND (C2.Reason_type="Discerned");


Is there anyway I can edit this code to allow for blank fields in the report while still being able to run a query?

 

[theDBguy]

Hi. Welcome to AWF!

Looking at your query, the first criteria I see is for the ConsultType column. Are you saying you want to also see records with empty ConsultTypes?

 

[plog]

I would strip all the criteria from your query. Then I would use either DoCmd.OpenForm or DoCmd.OpenReport to open the report or form you are trying to open (very unclear which it is). When you use the DoCmd.OpenX method you are able to construct a filter string to it and use it to limit the results seen on the form/report. Using that method you can include just the criteria present on the form.

Additionally, while you have used LEFT JOIN in your query, you have effectively converted it to an INNER JOIN by adding criteria to its fields.

 

[jdraw]

Can you tell us more about the application you are designing? The 30,000 ft overview would help clarify things.
A searchable report seems rare. A form that has ability to search and/or filter records that can be optionally forwarded to a report is more common.

 

[Pat Hartman]

If your fields might contain ZLS rather than null, then the solution is to replace ALL your IsNull()'s with:

[Forms]![FrmCriteria]![ConsultType] & "" = ""

That works whether the field is null or ZLS.

 

[Auntiejack56]

AND (IsNull([Forms]![FrmCriteria]![EndDate]) OR [Forms]![FrmCriteria]![EndDate] <= [Forms]![FrmCriteria]![EndDate])

that looks weird to me, and might be the reason all this is going haywire.
Another way to write it in SQL that works (which might help you) is, for example:

WHERE C1.Consult_Type=nz([Forms]![FrmCriteria]![ConsultType],C1.Consult_Type)

That said, the other folk have made better suggestions, such as building the string yourself and simply omitting the comparisons where the form field is blank, eg:

Dim strSQL As String

strSQL = "SELECT C1.Consult_ID, C1.Consult_No, C1.Consult_Type, C1.Intake_Date, C1.[Adult-Peds], C1.Nutshell, C1.Consult_Summary " _
    & "FROM Consults AS C1 LEFT JOIN Consult_Reasons AS C2 ON C1.Consult_ID = C2.Consult_ID " _
    & "WHERE 1=1;"


If [Forms]![FrmCriteria]![ConsultType] & "" = "" Then   ''  oops, should be If [Forms]![FrmCriteria]![ConsultType] & "" <> ""
    strSQL = strSQL & " AND C1.Consult_Type = [Forms]![FrmCriteria]![ConsultType]"
End If
If IsDate([Forms]![FrmCriteria]![StartDate]) Then
    strSQL = " AND C1.Intake_Date >= [Forms]![FrmCriteria]![StartDate]"
End If
''etc etc

Good luck,
Jack

 

[The_Doc_Man]

I once had a six-way filtering report and I made it a point to build the WHERE clause dynamically for subsequent use. If there was no selection for a given category, I build nothing corresponding to it. The six-way filter worked perfectly, but it also worked perfectly when fewer than six filter conditions were involved. The trick is building the WHERE clause. AuntieJack's 2nd code sample is similar to what I used - six times in sequence.

 

[CJ_London]

‘Is null()’ is not valid syntax, think what you should be trying is

(myfield=forms!srchfrm!txtbox OR forms!srchfrm!txtbox IS NULL)