Frederick has attended:
Excel Intermediate course
Excel Advanced course
Access Intermediate course
Excel VBA Introduction course
Excel VBA Intermediate course
Query returning all values if combo box null
I am currently using a query that checks values selected in 3 separate combo boxes with the fields in the table, if they match up, the query returns matching results (where all 3 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 alignment in the other two queries.
The 3 fields that are currently used in the query are:
1) User (User_check in the combo box)
2) Product (product in the combo box)
3) Trade Type (TradeType in the combo box)
Here is the code in SQL as it stands:
SELECT tblBusted.Date, tblBusted.Time, tblBusted.DealId, tblBusted.TradeType, tblSupervisors.User, tblBusted.Product, tblBusted.Date, tblBusted.Date
FROM tblSupervisors INNER JOIN tblBusted ON tblSupervisors.User = tblBusted.User
GROUP BY tblBusted.Time, tblBusted.DealId, tblBusted.TradeType, tblSupervisors.User, tblBusted.Product, tblBusted.Date, tblBusted.MarketId, tblBusted.Strip, tblBusted.BuyerName, tblBusted.BuyerCompany, tblBusted.SellerName, tblBusted.SellerCompany, tblBusted.Date
HAVING (((tblBusted.TradeType)=Forms!frmQueryState!TradeType) And ((tblSupervisors.User)=Forms!frmQueryState!User_check) And ((tblBusted.Product)=Forms!frmQueryState!product) And ((tblBusted.Date) Between Forms!frmQueryState!Fromdate And Forms!frmQueryState!Todate));
Any help would be appreciated.
Regards,
Frederick
RE: Query returning all values if combo box null
Hello Frederick,
Hope you enjoyed your Microsoft Excel and Access courses with BEST Training.
Thank you for your question regarding Access and SQL.
Your question is more complex than the forum caters for but if you would like us to take a deeper look, we would need to see your database and there may be time and costs invovled to come up with a solution.
I have asked my colleage to contact you regarding this.
Kind regards,
Richard
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.