Sorting Query by Multiselect Listbox

[ChronicFear]

Hello,

I've previously posted this question here: http://www.access-programmers.co.uk/forums/showthread.php?t=168702

but thought I would open it up to the rest of the forum for assistance.


I have a query that identifies categories of things that don't have any additional data entered about them. The query works fine when I run it solely as a query, but what I would like to do is run it based on user selection from a multiselect list box.

For example:

I have groups of things A, B, and C. They need data entered about them every quarter of every year. I want to run a report that could say in 2009, C is all done, A needs data in Q2 and Q3, and B needs data in Q4. So that would be running it by year. If I wanted to see which needs data in Q3, I want my query to return only A.

My current sql that works overall is:

SELECT tblProducts.Product, qryMissingGoals2.Product, tblProducts.Owner, [ProductManager_First] & " " & [ProductManager_Last] AS ProductManager, qryMissingGoals2.StrategyID, qryMissingGoals2.StrategyDescription, qryMissingGoals2.StrategyType, qryMissingGoals2.StrategyComponent, qryMissingGoals2.Status, qryMissingGoals2.Quarter, qryMissingGoals2.Year, qryMissingGoals2.Notes
FROM tblEmployees INNER JOIN (tblProducts LEFT JOIN qryMissingGoals2 ON tblProducts.Product = qryMissingGoals2.Product) ON tblEmployees.LAN_ID = tblProducts.Owner
GROUP BY tblProducts.Product, qryMissingGoals2.Product, tblProducts.Owner, [ProductManager_First] & " " & [ProductManager_Last], qryMissingGoals2.StrategyID, qryMissingGoals2.StrategyDescription, qryMissingGoals2.StrategyType, qryMissingGoals2.StrategyComponent, qryMissingGoals2.Status, qryMissingGoals2.Quarter, qryMissingGoals2.Year, qryMissingGoals2.Notes
HAVING (((qryMissingGoals2.Product) Is Null));

The sql for the other query referenced is:

SELECT tblStrategy.StrategyID, tblStrategy.StrategyDescription, tblStrategy.StrategyType, tblStrategy.Product, tblStrategy.StrategyComponent, tblStrategy.Status, tblStrategy.Quarter, tblStrategy.Year, tblStrategy.Notes
FROM tblStrategy
WHERE (((tblStrategy.Quarter)=[forms]![frmReportWizard].[Quarter]) AND ((tblStrategy.Year)=[forms]![frmReportWizard].[Year]));

Does anyone have any ideas?

Thanks,
CF

 

[Thinh]

i don't know exact how your table structure looks like but i would include a yes/no field at the end of table name complete. so when an entries consider complete flag this boolean fields to true so when you run your report specify that you want to see when complete = 0. that should return all uncompleted entries. there is another approach you can consider is to is the is null criteria in you queries. Most likely the records that a uncompleted has some field not filled in. if you know what fields that are required to have values in it you can just create a query to look for is null.