Solved

Access Query linked Form Dropdown options

Posted on 2014-01-10
7
275 Views
Last Modified: 2014-01-29
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
Comment
Question by:dnt2009
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39770641
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
 
LVL 30

Expert Comment

by:hnasr
ID: 39770714
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39770742
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 30

Expert Comment

by:hnasr
ID: 39770927
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39771028
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
 

Author Comment

by:dnt2009
ID: 39779540
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
 

Author Comment

by:dnt2009
ID: 39794016
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question