Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query results if criteria is blank

Status
Not open for further replies.

tjs32

Programmer
Jun 21, 2004
26
AU
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
 
Criteria is to be change to something like
Code:
[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!".
 
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
 
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!".
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top