Query returning all values if null

[Damasfred]

I am currently using a query that checks values selected in 2 separate combo boxes with the fields in the table, if they match up, the query returns matching results (where both fields line up). However, I wish to change the queries such that if a field is left blank in the combo box on the form, for that part of the query, all results are returned. eg. if there is no user value in the combo box, all users are returned subject to the product selection being correct. The 2 fields that are currently used in the query are: 1) User (User_check in the combo box) 2) Product (product in the combo box) Here is the code in SQL as it stands: SELECT tblSupervisors.User, tblBusted.Product FROM tblSupervisors INNER JOIN tblBusted ON tblSupervisors.User = tblBusted.User GROUP BY tblSupervisors.User, tblBusted.Product HAVING (((tblSupervisors.User)=[Forms]![frmQueryState]![User_check]) AND ((tblBusted.Product)=[Forms]![frmQueryState]![product])); Any help would be appreciated. Regards, Frederick

 

[Galaxiom]

SELECT DISTINCT tblSupervisors.User, tblBusted.Product 
FROM tblSupervisors 
INNER JOIN tblBusted 
  ON tblSupervisors.User = tblBusted.User
WHERE 
   (
   tblSupervisors.User=[Forms]![frmQueryState]![User_check] 
   OR [Forms]![frmQueryState]![User_check] = ""
   )
AND 
   (
    tblBusted.Product=[Forms]![frmQueryState]![product]
    OR [Forms]![frmQueryState]![product] = ""
   )
;

No need for Group By since you have no aggregation. DISTINCT has the same effect if you are getting duplicate records.

 

[arnelgp]

(((tblSupervisors.User)=IIF(Trim([Forms]![frmQueryState]![User_check] & "")="",tblSupervisors.User, [Forms]![frmQueryState]![User_check]))