[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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;
0
dnt2009
Asked:
dnt2009
  • 2
  • 2
  • 2
  • +1
1 Solution
 
mbizupCommented:
Try changing the criteria to this:

 tblItems.Itemstatus2 LIKE iif("" &  [Forms]![Main]![Combo60] = "ALL", "*",  [Forms]![Main]![Combo60])

ie:
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 LIKE iif("" &  [Forms]![Main]![Combo60] = "ALL", "*",  [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;

Open in new window

0
 
hnasrCommented:
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

0
 
Dale FyeCommented:
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)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
hnasrCommented:
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?
0
 
Dale FyeCommented:
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

0
 
dnt2009Author Commented:
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!
0
 
dnt2009Author Commented:
What code can I had to the above query when no value is selected from the drop-down to showw all data?

Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now