Selecting "All" in a combobox

[cvincent78]

Folks,

Need help getting my code to work. I am trying to select "All" in a combobox for a query in access.

See my sql code I added to the Row Source:

Select Managers AS Filter, Managers FROM dbo_ManagerList UNION SELECT '*' AS Filter, " All" AS Managers FROM dbo_ManagerList GROUP BY dbo_ManagerList.Managers
ORDER BY dbo_ManagerList.Managers;

Bound Column = 1
Column Count = 2
Column Widths = 0; 2

Looks like the query builder is working correctly but not translating to the actual query I am trying to configure. My normal values work just not the all function.

Any assistance would be highly appreciated.

Thanks,

Chris

[PhilOfWalton]

This SQL assumes the bound column is 2 (MemberId - Primary Key), It is a sample but you can adjust it as required

    StrSQL = "SELECT " & Chr$(34) & "<All>" & Chr$(34) & " AS MemSurName, " & Chr$(34) & Chr$(34) & " AS MemFirstName, 0 AS MemberID, 0 AS SortOrder FROM Member "
    StrSQL = StrSQL & "UNION SELECT MemSurName, MemFirstName, MemberID, MemSurName AS SortOrder From Member "
    StrSQL = StrSQL & "ORDER BY SortOrder, MemSurName, MemFirstName;"

Phil

[NeoPa]

Hi Chris.

It seems your problem is that your GROUP BY and ORDER BY clauses, that are outside of the UNION part remember, use expressions qualified to only one side of the UNION.

Try using :

...
GROUP BY [Managers]
ORDER BY [Managers]

[cvincent78]

Gents,

Thanks for the feedback... unfortunately neither worked?

Is there any other details I can post that might help you find my mistake?

Thanks

[NeoPa]

Please post the exact SQL you tried based on my suggestion for us to consider.

[cvincent78]

See exact code below:

Select Managers AS Filter, Managers FROM dbo_ManagerList UNION SELECT '*' AS Filter, "All" AS Managers FROM dbo_ManagerList GROUP BY Managers
ORDER BY Managers;

Query builder looks to be correct:

Showing to columns one called Filter the other Managers with the same rows + included "All" row.

Thanks Neo

[NeoPa]

CVincent78:
Showing to columns one called Filter the other Managers with the same rows + included "All" row.

Are you now saying that works after all?
Or should I still be looking to see where this version fails?

[cvincent78]

Nope still doesn't work.

[twinnyfo]

I guess I'm just throwing this out there, but, doesn' the syntax SELECT '*' AS Filter imply (in this particular situation with the UNION QUERY), that there is only one field in dbo_ManagerList, and that if there are multiple fields in that table, this will cause problems with the UNION?

Or am I totally off base, with this one?

[cvincent78]

There is only one field in the dbo_ManagerList . When I run the SQL script in the combobox query it creates the filter column and add the "All" row to both

[PhilOfWalton]

You apparently made no attempt to modify the SQL I sent you which I know works.
Right, now we have your table name and assuming you have a primary key called ManagerID and a field called ManagerNamecthe n try

StrSQL = "SELECT " & Chr$(34) & "<All>" & Chr$(34) & " AS ManagerName, " ,
StrSQL = StrSQL & "0 AS ManagerID, 0 AS SortOrder FROM dbo_ManagerList "
StrSQL = StrSQL & "UNION SELECT ManagerName,  ManagerID, 
StrSQL = StrSQL & "ManagerName AS SortOrder From dbo_ManagerList "
StrSQL = StrSQL & "ORDER BY SortOrder, ManagerName;"

Phil

[cvincent78]

Phil,

Definitely tried your code.

There is no ManagerID column. There is only a dbo table called dbo_ManagerList and a column named [Managers] with only 5 manager names for rows.

I modified your code (See Below)

1.	StrSQL = "SELECT " & Chr$(34) & "<All>" & Chr$(34) & " AS Manager, " ,
2.	StrSQL = StrSQL & "0 AS Managers, 0 AS SortOrder FROM dbo_ManagerList "
3.	StrSQL = StrSQL & "UNION SELECT Managers,  
4.	StrSQL = StrSQL & "Managers AS SortOrder From dbo_ManagerList "
5.	StrSQL = StrSQL & "ORDER BY SortOrder, Managers;"
6.	 StrSQL = "SELECT " & Chr$(34) & "<All>" & Chr$(34) & " AS Managers, " ,

Gives me a Invalid SQL statement; 'Delete', 'Insert', 'Procedure', 'Select' or 'UPDATE'.

Thanks for trying to walk me through this.

[NeoPa]

Please try this - and if it doesn't work then tell us what does go wrong. Trying to guess what's wrong while you observe and wait isn't any better use of your time than it is ours ;-)

SELECT   [Managers] AS [Filter]
       , [Managers]
FROM     [dbo_ManagerList]
UNION
SELECT   '*' AS [Filter]
       , 'All' AS [Managers]
GROUP BY [Managers]
ORDER BY [Managers]

[cvincent78]

Neo,

I added the code exactly the way you posted and I get a SELECT statement includes a reserved word or an argument name that is misspelled, or the punctuation is incorrect.

Maybe I should step back and explain what I am trying to do in more detail.

I have a main form page that I have a combo box that pulls from a dbo table of manager names. (The Row Source) When the user selects the name of the manager they want it will change the manager in a query to that name and update a subform report graph of total back log hours. Everything works perfectly, but I would also like to see all the managers total back log hours. This is where I am stuck it seems like the query used to update the graph isn't accepting the wildcard '*'... hope this explains things better. Once again I appreciate your guy's time in helping me resolve.

[Rabbit]

How are you trying to use this wildcard? Because Field = '*' is not going to work. You need to use the LIKE operator.