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?
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?