Option to leave part of a searchable report/form blank in Microsoft access-CodePudding

I have a query in MS Access that is linked to a searchable report which is linked to a form. Part of the report ask users to include two reasons that will be used as criteria for a query. Currently, the report (screenshot) below does not allow for users to simply enter in 1 reason. Users must enter in 2 reasons, and if the reasons are the same, users must enter in the same reason for both fields. For example, If I only have one reason (conflict of Interest),I would need to enter that reason into both reason boxes shown on the screenshot below. I would like to be able to search for one reason while also being able to leave the other reason box blank. So simply entering in Conflict of interest for one box and have it run the query. The code I have for the query is below.

SELECT C1.ConsultID, C1.Consult_No, C1.Consult_Type, C1.Intake_Date, C1.Adult_Peds, C1.Title, C1.ConsultSummary
FROM ([Intersection Query Pre-Reasons] AS C1 LEFT JOIN tblConsultReasons AS C2 ON C1.ConsultID = C2.ConsultID) LEFT JOIN tblConsultReasons AS C3 ON C2.ConsultID = C3.ConsultID
WHERE (((C2.Reason)=Forms!FrmReasonsCriteria!Reason1) And ((C2.ReasonType)="Discerned")) And (C3.Reason)=Forms!FrmReasonsCriteria!Reason2 And C3.ReasonType="Discerned";

How would you recommend I change the code in order to do this. I believe adding a length condition to the second half of the where clause may work. But am not sure what this would look like.

Screenshot of part of the searchable report that queries on reasons

CodePudding user response：

It will be something like this; I haven't tested it because that requires building forms etc and I don't have the time atm:

SELECT C1.ConsultID, C1.Consult_No, C1.Consult_Type, C1.Intake_Date, C1.Adult_Peds, C1.Title, C1.ConsultSummary
FROM ([Intersection Query Pre-Reasons] AS C1 
      LEFT JOIN tblConsultReasons AS C2 ON C1.ConsultID = C2.ConsultID) 
      LEFT JOIN tblConsultReasons AS C3 ON C2.ConsultID = C3.ConsultID
WHERE
    NOT (nz(Forms!FrmReasonsCriteria!Reason1,'')='' And
         nz(Forms!FrmReasonsCriteria!Reason2,'')='' 
        )
    And (
           (nz(C2.Reason=Forms!FrmReasonsCriteria!Reason1,'')='' 
            And C2.ConsultID is null) 
            Or
           (nz(C2.Reason=Forms!FrmReasonsCriteria!Reason1,'')<>''  And
            C2.Reason=Forms!FrmReasonsCriteria!Reason1 And 
            C2.ReasonType="Discerned")
        )           
    And (
           (nz(C3.Reason=Forms!FrmReasonsCriteria!Reason2,'')='' 
            And C3.ConsultID is null) 
            Or
           (nz(C3.Reason=Forms!FrmReasonsCriteria!Reason2,'')<>''  And
            C3.Reason=Forms!FrmReasonsCriteria!Reason2 And 
            C3.ReasonType="Discerned")
        )

It can be simplified a bit if we know the data available, and other controls/checks the search boxes may have.