Query help (1 Viewer)

[jrjr]

What would I change in this query so that if one or both combos were left blank, the records for the combo(s) would ALL be returned to my form? This works now but I need a value in both combos or I get no records returned.


SELECT tblWO.WOnumber, tblWO.Status, tblWO.Property, tblWO.Description, tblWO.Requested, tblWO.RequestedBy, tblWO.Service, tblWO.Created, tblWO.Phone, tblWO.Asset
FROM tblWO
WHERE (((tblWO.Status)=[Forms]![FrmFilter]![FrmFilterCombo]) AND ((tblWO.Property)=[Forms]![FrmFilter]![PropertyFilter])) OR (((tblWO.Property)=[Forms]![FrmFilter]![PropertyFilter]) AND (([Forms]![FrmFilter]![FrmFilterCombo])="Is Null")) OR (((tblWO.Status)=[Forms]![FrmFilter]![FrmFilterCombo]) AND (([Forms]![FrmFilter]![PropertyFilter])="Is Null")) OR ((([Forms]![FrmFilter]![FrmFilterCombo])="Is Null") AND (([Forms]![FrmFilter]![PropertyFilter])="Is Null"));

 

[Jon K]

Take a look at the sample database in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=84213

.

 

[jrjr]

Thanks for the info. After looking at the db I changed my query to this but I am getting event prompts:

SELECT tblWO.WOnumber, tblWO.Status, tblWO.Property, tblWO.Description, tblWO.Requested, tblWO.RequestedBy, tblWO.Service, tblWO.Created, tblWO.Phone, tblWO.Asset, [Event]=[forms]![FrmFilter]![FrmFilterCombo] Or [forms]![FrmFilter]![FrmFilterCombo] Is Null AS Expr1, [Event]=[forms]![FrmFilter]![PropertyFilter] Or [forms]![FrmFilter]![PropertyFilter] Is Null AS Expr2
FROM tblWO
WHERE (((tblWO.Status)=[Forms]![FrmFilter]![FrmFilterCombo]) AND ((tblWO.Property)=[Forms]![FrmFilter]![PropertyFilter])) OR (((tblWO.Property)=[Forms]![FrmFilter]![PropertyFilter])) OR (((tblWO.Status)=[Forms]![FrmFilter]![FrmFilterCombo]));

 

[Jon K]

You can rebuild your query. In query Design View, put the criteria for Status and Property each in a new column like this:-

-------------------------------------
Field: Status=[Forms]![FrmFilter]![FrmFilterCombo] or [Forms]![FrmFilter]![FrmFilterCombo] is null

Show: uncheck

Criteria: True

-------------------------------------
Field: Property=[Forms]![FrmFilter]![PropertyFilter] or [Forms]![FrmFilter]![PropertyFilter] is null

Show: uncheck

Criteria: True

--------------------------------------
.

 

[jrjr]

Yes indeed that worked great! Thank you
I also have a listbox that picks the rs and displays all records filtered by the combos. Clicking a record in the listbox populates the form with the chosen record and closes the listbox. Now, it will still populate the listbox if both combos have a value or if status has a value but if status is empty and property has a value there are no records in the listbox. I have been messing with isnull in the recordsource for the listbox but can't seem to get it right. Wanna take a look? I have this in the onclick of a command button:

original:
Me.Listbox1.RowSource = "SELECT [tblWO].[WOnumber], [tblWO].[Requested], [tblWO].[RequestedBy], [tblWO].[Status], [tblWO].[property], [tblWO].[asset] " & _
"From tblWO " & _
"Where [tblWO].[status] = """ & [Forms]![FrmFilter]![FrmFilterCombo] & """ And " & _
"[tblWO].[Property] = """ & [Forms]![FrmFilter]![PropertyFilter] & """;"


I got this far. Status works but Property returns no records in the listbox.

Me.Listbox1.RowSource = "SELECT [tblWO].[WOnumber], [tblWO].[Requested], [tblWO].[RequestedBy], [tblWO].[Status], [tblWO].[property], [tblWO].[asset] " & _
"From tblWO " & _
"Where [tblWO].[status] = """ & [Forms]![FrmFilter]![FrmFilterCombo] & """ or [tblWO].[status] is null And " & _
"[tblWO].[Property] = """ & [Forms]![FrmFilter]![PropertyFilter] & """ or [tblWO].[Property] is null ;"

Plus, now the listbox does not refresh with the new info on change of the combo. I have to close the listbox and open it again and then the info is there. I have added Me.listbox1.Requery to both the onchange and the after update of the combos but to no avail. Feels more like a Monday than a Friday! LOL

 

[jrjr]

I am still working on this... any suggestions out there?

 

[EMP]

Jon K has shown you how you can build the query in Design View so that if one or both combos were left blank, the records for the combo(s) would ALL be returned.

You can switch the query to SQL View to see how Access constructs the SQL statement. The two IS NULLs reference the combos on the form, not the table fields. And there are two =True there in the SQL statement.

 

[jrjr]

Thank you for the hints! I was able to sort it out I believe. Tomorrow at work I will apply the changes to the project and see how it goes. Thanks again!