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 ManagerNamecthen 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.

[cvincent78]

Rabbit,

Using the SQL wildcard in the SQL builder of the row source for the combobox.

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

If I pick one of the managers the query which is mapped to the combo box updates correctly but if I pick the "All" row the query goes blank and acts like it doesn't recognize the wildcard.

[Rabbit]

That's how you're populating the drop-down list. That's not what I'm asking about, how are you trying to make use of the value they select?

[cvincent78]

Pretty sure it is outlined here:

UNION SELECT '*' AS Filter,

But I could easily be wrong....

[Rabbit]

That is how you're adding the asterisk to the drop down.

That's not what I'm looking for. I'm looking for how you make use of that asterisk to filter the records.

[cvincent78]

I thought that the union function would insert the wildcard into the combo mapping in the query when selecting "All" if it doesn't then I think we found my problem. If this is true this is the part I need help with lol

[zmbd]

Ok, so I have a very simply union query that I use a combobox row source to select a year from one of my databases for sample reports:

SELECT ZUNION.ZSelectYear
FROM 
  (SELECT 1 as ZOrder
    ,  "All" as ZSelectYear 
   FROM t_testyear 
   UNION SELECT 2 as Zorder
    , t_testYear.PK_testYear AS "Zyear" 
   FROM t_testYear)  AS ZUNION
ORDER BY ZUNION.Zorder, ZUNION.ZSelectYear DESC;

On the form I have a control button [on_click] event

Private Sub zctrl_btn_OK_Click()
  If [TempVars]![reportyears] & "" = "" Then
    [TempVars].Add "ReportYears", "*"
  End If
  '
  If [TempVars]![openreportfor] & "" = "" Then
    [TempVars].Add "OpenReportFor", "1"
  End If
  '
  If Not Me.zctrl_cbo_selectyear = "All" Then
    [TempVars]![reportyears] = Me.zctrl_cbo_selectyear.Value
  Else
    [TempVars]![reportyears] = "*"
  End If
  [TempVars]![openreportfor] = Me.zctrl_option_OpenReportFor.Value
  '
  DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

That's only one way to handle the CBOX return so your mileage may vary :)

I'm using the [TempVars] object to pass the year value to the query that my report is based on. I use this same report to show the results for a single sample as well as the group of samples - the query is quite long but the basic idea would be

SELECT (a bunch of fields here)
FROM (a bunch of joins here)
WHERE 
 ((t_testkit.fk_testYear) Like [TempVars]![ReportYears])
   AND (other [TempVar] references));

[Rabbit]

would insert the wildcard into the combo mapping in the query when selecting "All"

It will, but that doesn't mean the query is set up to use it correctly. That's why I was asking how you're trying to use it. To make sure you set up that query correctly.

We still need to see that btw.

[NeoPa]

CVincent:
If this is true this is the part I need help with lol

Yes. That would make sense.

How about you tell us what you're using at the moment. Typically people often try to create SQL that filters on ='*' or ='{Selected Value}' if you prefer. This won't work as a wildcard as that only works as expected when using the Like keyword.

So, it should be more like ... Like '{Selected Value}'. Let us know if you still have problems once you've looked at that.

BTW, I discovered after setting up a test to match your system that using a values-only SELECT query, which is quite valid normally, is not valid in Jet/ACE SQL when using UNION. That's why my code didn't work and using the extra FROM and GROUP BY clauses was required - unfortunately. It seems that wasn't really the question you were trying to ask though. I just wanted to explain why you were getting what you were getting.

[cvincent78]

Morning Folks,

First totally appreciate your guys efforts. Neo I tried the LIKE operator a couple days ago but SQL wont let me use it after a SELECT operator.

Rabbit,

See the posted query below:

SELECT dbo_KAI_Project_Tracker_Table.AssignedEngineer, dbo_KAI_Project_Tracker_Table.ProjectName, IIf([EngHoursremaining]<0,[EstimatedHourstoComplete],[EngHoursRemaining]) AS Hours, dbo_KAI_Project_Tracker_Table.CustomerName, dbo_KAI_Project_Tracker_Table.CostCategory, dbo_KAI_Project_Tracker_Table.Status, dbo_KAI_Project_Tracker_Table.Division, [dbo_Staff List].Manager
FROM dbo_KAI_Project_Tracker_Table INNER JOIN ([dbo_Staff List] INNER JOIN dbo_ManagerList ON [dbo_Staff List].Manager = dbo_ManagerList.Managers) ON dbo_KAI_Project_Tracker_Table.AssignedEngineer = [dbo_Staff List].Staff
WHERE (((dbo_KAI_Project_Tracker_Table.AssignedEngineer) Is Not Null And (dbo_KAI_Project_Tracker_Table.AssignedEngineer) Not Like "Not assigned" And (dbo_KAI_Project_Tracker_Table.AssignedEngineer) Not Like "mfg Engineering") AND ((dbo_KAI_Project_Tracker_Table.CustomerName)<>"Gleason Works") AND ((dbo_KAI_Project_Tracker_Table.Status)<>"complete") AND (([dbo_Staff List].Manager)=[Forms]![FRM_MainPage]![CB_ManagerFilter]));

As discussed I have a combo box on a form page that updates a query and then a report (The report is a subform on the main form) to reflect engineering hours by manager. The intent of this trilogy is to be able to select "All" So I can see the total backlog hours for the engineering platform as a whole.

Again appreciate everyone's time... hopefully we solve it and save the next guy some time.

C.

[twinnyfo]

I believe what NeoPa was referring to was having the Like operator in your WHERE statement. When you place “*” directly after SELECT you are saying you want all FIELDS. I think what you want is all RECORDS that meet certain criteria. Thus:

WHERE [FieldName] Like 'Criteria*'

[Rabbit]

This is what I was talking about earlier.

[dbo_Staff List].Manager)=[Forms]![FRM_MainPage]![CB_ManagerFilter]

You can't use an equals with wildcards. They only work with the LIKE operator.

[cvincent78]

Alright gents,

I replaced the = with LIKE in the Query. Which one interesting thing is when I first open the database it shows all managers, but once I pick "All" it goes back to blank. Picking individual manager still works.

Twinny,

I added:

WHERE [Managers] LIKE "criteria*" 

To the rowsource SQL script but it didn't make any difference.

Tried it on both sides of the UNION function.

Thoughts?

[NeoPa]

Hi Chris.

I was hoping you wouldn't be misled by Twinny's post #25. You don't add an asterisk (*), nor do you use a literal string value. I believe it was intended more as an illustration than an instruction per se.

You either use a direct reference to the form control within the SQL (Similar to Rabbit's suggestion but don't rely on his bracketing.), or you build the SQL something like :

strSQL = "SELECT Blah, blah, blah " _
       & "FROM   [YourTable]
       & "WHERE  ([Manager] Like '%V')"
strSQL = Replace(strSQL, "%V", Me.cboManagerFilter)

Assuming the code is associated with the Form itself and the ComboBox control is called [cboManagerFilter].

[cvincent78]

Neo,

Do I add this into the rowsource SQL code or the Query SQL code?

Thanks so much guys.... I have learned a lot in the last couple days.

C.

[PhilOfWalton]

This has gone on far too long.

Firstly your dbo_ManagerList should have a ManagerID as an Autonumber Primary Key. Why? It is conceivable that you may have 2 managers with the same name.

Use the SQL I posted earlier (Post 11) as the RowSource of the Combo Box.

Ensure all your forms & Reports contain the Field "ManagerID"

The whatever form or report you want to filter, build some code like

Sub CB_ManagerFilter_AfterUpdate
 
    Dim Fltr as String
 
    With Me.SubformName.Form       ' This is the name of the 
                                   ' Subform on Your Main Form
        If ManagerID = 0 then      ' <All> selected
            .FilterOn = False      ' No Filter
        Else
            Fltr = "ManagerID = " & CB_ManagerFilterCB  ' Combo
            ' Box (The Cound Column)
            .FilterOn = True       ' Apply the Filter
        End If
    End With
End Sub
 

Phil

[NeoPa]

CVincent78:
Do I add this into the rowsource SQL code or the Query SQL code?

You have to understand what is being explained here and use this understanding to create the SQL used to filter your object, whatever that is, that needs to be filtered based on this selection.

This could be by setting a .Filter property of a Form or Report, or it could be to change the WHERE clause of some query.

Only you know what that is, but the concept has been explained clearly and it's now down to you to understand that and build on what you have with that new understanding.

We aren't here to spoon-feed you. At some point you need to grab the concept and go with it. That's now.

We can still help, but we do expect you to get with it and start to work things out for yourself when you can. We're pretty well at that point now.

[cvincent78]

Thanks guys.. going to tackle it this weekend.

I will let you know how it works out.

[NeoPa]

8-)
Good luck :-)

[cvincent78]

Alright got it! Looks like my original code as well as NeoPa's code in the SQL builder was correct all along :)
My problem was my subform was a report I used to build a chart. Once I deleted the subform and added a chart using the Query (Qry_EngHoursbyDivision) directly on the main form (Frm_MainPage) everything worked perfect.

Hope that made sense folks... appreciate everyone's time. Guessing this is the reason everyone else's solution didn't work, but I don't have the time to look into it today.

C.

[NeoPa]

Glad to hear it's all working well now.

Cheers.