Query results if criteria is blank

[tjs32]

I have 2 drop down boxes on a form to identify criteria for the report. What I want is, if the user leaves the drop down blank, I want it to return all results. I tried the <All> option suggested in a FAQ however this does not work with the records that have this particular field blank. I need it to return all records including blanks OR the criteria selected from the drop down. Here is my query


SELECT tblClients.PartnerID, tblClients.Type, tblClients.CompanyName, [FirstName] & " " & [LastName] AS Name, tblContactLog.ContactDate, tblContactLog.PartnerID, tblClientContactDetails.PhoneWk
FROM (tblClients INNER JOIN tblContactLog ON tblClients.ClientID = tblContactLog.ClientID) INNER JOIN tblClientContactDetails ON tblClients.ClientID = tblClientContactDetails.ClientID
WHERE (((tblClients.PartnerID) Like [Forms]![frmReportCriteria]![Combo0]) AND ((tblClients.Type)=[Forms]![frmReportCriteria]![Combo2]) AND ((tblContactLog.ContactDate) Between [Forms]![frmReportCriteria]![Text4] And [Forms]![frmReportCriteria]![Text6]));

If the combos are left blank this comes up blank.
Thanks

 

[ZmrAbdulla]

Criteria is to be change to something like

[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null

________________________________________________________________________
Zameer Abdulla
Visit Me
By the time a man realizes that may be his father was right,
he usually has a son who says, "Father, you are wrong!".

 

[tjs32]

ZmrAbdulla

Thank you for your prompt response. I had already tried this solution and it still returned nothing, however when I tried it again after your response and I tried it only on one field and deleted the criteria on the other fields it worked, so I need to look at the criteria on the other fields.

Thank you

 

[ZmrAbdulla]

How do you pass multiple textbox values to query criteria when one or more values may be null?
faq701-3177
Multiple Search Criteria Blank - All records returned
thread701-990510
may help you..

________________________________________________________________________
Zameer Abdulla
Visit Me
By the time a man realizes that may be his father was right,
he usually has a son who says, "Father, you are wrong!".