asked on

Access Query linked Form Dropdown options

Hi all,

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.

Many thanks,
dnt

Query:

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;

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

Compare with this:
Table: a(aid, ...)
Form: _tutorial
ComboBox: cboBox1, displaying a.aid values plus All.

SELECT a.aid from a, ...
where aid like Switch(forms![_tutorial].cboBox1="All","*",forms![_tutorial].cboBox1<>"All",forms![_tutorial].cboBox1);

Open in new window

Dale Fye

I prefer to use something along the lines of:

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]  
OR [Forms]![Main]![Combo60] = "ALL")
AND tblItems.BusinessChannel =[Forms]![Main]![Combo57])  AS q2
ON (u.ItemID = q2.ItemID) AND (u.DateOfUpdate = q2.MaxOfDateofUpdate)
ORDER BY q2.BusinessChannel, q2.ItemCategory;

Open in new window

I added the left paren on line 13 and all of line 14)

Hamed Nasr

fyed,

I like this idea,
>WHERE (tblItems.Itemstatus2 =[Forms]![Main]![Combo60] OR [Forms]![Main]![Combo60] = "ALL")
But may not work if comparing with numeric field. Did you check that?

Dale Fye

hnasr,

Good point. If the bound column is not the text value, then I generally associate a zero with the "All" option. In that case, I generally use:

OR [Forms]![Main]![Combo60] = 0)

Open in new window

dnt2009

ASKER

Hi all.

Thank you for all your suggestions!
I went with the first one by mbizup and it works perfectly.

Many thanks to all again!

dnt2009

ASKER

What code can I had to the above query when no value is selected from the drop-down to showw all data?

Thanks