Access Query linked Form Dropdown options
Posted on 2014-01-10
I have the following query. Linked to a form dropdown box.
The user is to make a selection on the Main form which will feed the query and run a report.
Selection for tblItems.Itemstatus2 are: Option1, Option2, Option3, All.
I would like the query to show all data for tblItems.Itemstatus2 = All.
"All" is not a value in the field it's an option on the form. However I don't know how to translated for the query.
Your help will be greatly appreciated.
SELECT u.ItemUpdate, u.*, q2.*
FROM tblItemsUpdates AS u INNER JOIN (SELECT tblItems.*, q.MaxOfDateofUpdate FROM tblItems INNER JOIN (SELECT tblItemsUpdates.ItemID, MAX(tblItemsUpdates.DateOfUpdate) AS MaxOfDateofUpdate FROM tblItemsUpdates GROUP BY tblItemsUpdates.ItemID) AS q ON q.ItemID = tblItems.ItemID WHERE tblItems.Itemstatus2 =[Forms]![Main]![Combo60] AND tblItems.BusinessChannel =[Forms]![Main]![Combo57]) AS q2 ON (u.ItemID = q2.ItemID) AND (u.DateOfUpdate = q2.MaxOfDateofUpdate)
ORDER BY q2.BusinessChannel, q2.ItemCategory;