Return all results with parameters left blank

[Rowen]

Hi I wonder if someone can help me, I'm making some amendments to an existing database, and I seem to have broken a query. The query below is supposed to return results based on either whats selected in a combo box (and then put into a text field) or if non are selected return all results. However at the moment if I don't enter any parameters then no results are returned, and I'm pretty sure it was working and suddenly it seems to have stopped. (I'm also adding in another similar query so if I can get this one to work than I can fix the one I'm writing.)

At the end of the day I could use some code to replace the Where clause and get it to work that way, but it's just bothering me that the query was working but now seems to have stopped. So any help would be appriciated.

Thanks

SELECT tblTECases.TECaseNo, tblTECases.ReportedDate, tblParticipants.Classification, tblTenants.Title, tblTenants.FirstName, tblTenants.Surname, tblTenants.FullAddress, tblTECases.CaseType, tblTECases.TEOfficerID, tblOfficerName.TeamName, tblTenants.ContractArea
 
FROM tblOfficerName INNER JOIN (tblTenants INNER JOIN (tblTECases INNER JOIN tblParticipants ON tblTECases.TECaseNo=tblParticipants.TECaseNo) ON tblTenants.PersonID=tblParticipants.PersonID) ON tblOfficerName.OfficerName=tblTECases.TEOfficerID
 
WHERE (tblTECases.CaseType) Like Forms!frmMainReporter!txtType And (tblTECases.TEOfficerID) Like Forms!frmMainReporter!txtName And (tblOfficerName.TeamName) Like Forms!frmMainReporter!txtTeam And (tblTenants.ContractArea) Like Forms!frmMainReporter!txtContractArea And ((tblTECases.ClosedDate) Is Null);

 

[Simon_MT]

You need to add the wildcard into your query ie & "*" so that the query does not care whether or not the fields are populated.

Simon

 

[chrisuff]

I'm working on the same problem - Simon, where should the wild card go in the query design grid? Currently I have:
Between [Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate]
in the criteria window.
Many thanks,
Chris

 

[Rowen]

You need to add the wildcard into your query ie & "*" so that the query does not care whether or not the fields are populated.

Simon

I did try that but then when I did select a specific parameter from one of the combo boxes it still returned everything. But I'll try it again and see.

Edit: Nvm, I added them in and the queries seem to be working now!

 

[Simon_MT]

Great!

Your search should start with everything and the user choices are only there to narrow down the selection of records.

Simon