422,952 Members | 2,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,952 IT Pros & Developers. It's quick & easy.

Selecting "All" in a combobox

P: 15
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:
Expand|Select|Wrap|Line Numbers
  1. Select Managers AS Filter, Managers FROM dbo_ManagerList UNION SELECT '*' AS Filter, " All" AS Managers FROM dbo_ManagerList GROUP BY dbo_ManagerList.Managers
  2. 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
1 Week Ago #1

✓ answered by 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.

Share this Question
Share on Google+
34 Replies


PhilOfWalton
Expert 100+
P: 1,202
This SQL assumes the bound column is 2 (MemberId - Primary Key), It is a sample but you can adjust it as required

Expand|Select|Wrap|Line Numbers
  1.     StrSQL = "SELECT " & Chr$(34) & "<All>" & Chr$(34) & " AS MemSurName, " & Chr$(34) & Chr$(34) & " AS MemFirstName, 0 AS MemberID, 0 AS SortOrder FROM Member "
  2.     StrSQL = StrSQL & "UNION SELECT MemSurName, MemFirstName, MemberID, MemSurName AS SortOrder From Member "
  3.     StrSQL = StrSQL & "ORDER BY SortOrder, MemSurName, MemFirstName;"
  4.  
Phil
1 Week Ago #2

NeoPa
Expert Mod 15k+
P: 30,869
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 :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. GROUP BY [Managers]
  3. ORDER BY [Managers]
1 Week Ago #3

P: 15
Gents,

Thanks for the feedback... unfortunately neither worked?

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

Thanks
1 Week Ago #4

NeoPa
Expert Mod 15k+
P: 30,869
Please post the exact SQL you tried based on my suggestion for us to consider.
1 Week Ago #5

P: 15
See exact code below:

Expand|Select|Wrap|Line Numbers
  1. Select Managers AS Filter, Managers FROM dbo_ManagerList UNION SELECT '*' AS Filter, "All" AS Managers FROM dbo_ManagerList GROUP BY Managers
  2. 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
1 Week Ago #6

NeoPa
Expert Mod 15k+
P: 30,869
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?
1 Week Ago #7

P: 15
Nope still doesn't work.
1 Week Ago #8

twinnyfo
Expert Mod 100+
P: 2,443
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?
1 Week Ago #9

P: 15
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
1 Week Ago #10

PhilOfWalton
Expert 100+
P: 1,202
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

Expand|Select|Wrap|Line Numbers
  1. StrSQL = "SELECT " & Chr$(34) & "<All>" & Chr$(34) & " AS ManagerName, " ,
  2. StrSQL = StrSQL & "0 AS ManagerID, 0 AS SortOrder FROM dbo_ManagerList "
  3. StrSQL = StrSQL & "UNION SELECT ManagerName,  ManagerID, 
  4. StrSQL = StrSQL & "ManagerName AS SortOrder From dbo_ManagerList "
  5. StrSQL = StrSQL & "ORDER BY SortOrder, ManagerName;"
  6.  
Phil
1 Week Ago #11

P: 15
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)

Expand|Select|Wrap|Line Numbers
  1. 1.    StrSQL = "SELECT " & Chr$(34) & "<All>" & Chr$(34) & " AS Manager, " ,
  2. 2.    StrSQL = StrSQL & "0 AS Managers, 0 AS SortOrder FROM dbo_ManagerList "
  3. 3.    StrSQL = StrSQL & "UNION SELECT Managers,  
  4. 4.    StrSQL = StrSQL & "Managers AS SortOrder From dbo_ManagerList "
  5. 5.    StrSQL = StrSQL & "ORDER BY SortOrder, Managers;"
  6. 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.
1 Week Ago #12

NeoPa
Expert Mod 15k+
P: 30,869
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 ;-)
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Managers] AS [Filter]
  2.        , [Managers]
  3. FROM     [dbo_ManagerList]
  4. UNION
  5. SELECT   '*' AS [Filter]
  6.        , 'All' AS [Managers]
  7. GROUP BY [Managers]
  8. ORDER BY [Managers]
1 Week Ago #13

P: 15
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.
1 Week Ago #14

Rabbit
Expert Mod 10K+
P: 12,230
How are you trying to use this wildcard? Because Field = '*' is not going to work. You need to use the LIKE operator.
1 Week Ago #15

P: 15
Rabbit,

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

Expand|Select|Wrap|Line Numbers
  1. Select Managers AS Filter, Managers FROM dbo_ManagerList UNION SELECT '*' AS Filter, "All" AS Managers FROM dbo_ManagerList GROUP BY Managers
  2. ORDER BY Managers;
  3.  
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.
1 Week Ago #16

Rabbit
Expert Mod 10K+
P: 12,230
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?
1 Week Ago #17

P: 15
Pretty sure it is outlined here:

Expand|Select|Wrap|Line Numbers
  1. UNION SELECT '*' AS Filter,
But I could easily be wrong....
1 Week Ago #18

Rabbit
Expert Mod 10K+
P: 12,230
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.
1 Week Ago #19

P: 15
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
1 Week Ago #20

zmbd
Expert Mod 5K+
P: 5,105
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT ZUNION.ZSelectYear
  2. FROM 
  3.   (SELECT 1 as ZOrder
  4.     ,  "All" as ZSelectYear 
  5.    FROM t_testyear 
  6.    UNION SELECT 2 as Zorder
  7.     , t_testYear.PK_testYear AS "Zyear" 
  8.    FROM t_testYear)  AS ZUNION
  9. ORDER BY ZUNION.Zorder, ZUNION.ZSelectYear DESC;
On the form I have a control button [on_click] event
Expand|Select|Wrap|Line Numbers
  1. Private Sub zctrl_btn_OK_Click()
  2.   If [TempVars]![reportyears] & "" = "" Then
  3.     [TempVars].Add "ReportYears", "*"
  4.   End If
  5.   '
  6.   If [TempVars]![openreportfor] & "" = "" Then
  7.     [TempVars].Add "OpenReportFor", "1"
  8.   End If
  9.   '
  10.   If Not Me.zctrl_cbo_selectyear = "All" Then
  11.     [TempVars]![reportyears] = Me.zctrl_cbo_selectyear.Value
  12.   Else
  13.     [TempVars]![reportyears] = "*"
  14.   End If
  15.   [TempVars]![openreportfor] = Me.zctrl_option_OpenReportFor.Value
  16.   '
  17.   DoCmd.Close acForm, Me.Name, acSaveNo
  18. 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

Expand|Select|Wrap|Line Numbers
  1. SELECT (a bunch of fields here)
  2. FROM (a bunch of joins here)
  3. WHERE 
  4.  ((t_testkit.fk_testYear) Like [TempVars]![ReportYears])
  5.    AND (other [TempVar] references));
1 Week Ago #21

Rabbit
Expert Mod 10K+
P: 12,230
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.
1 Week Ago #22

NeoPa
Expert Mod 15k+
P: 30,869
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.
1 Week Ago #23

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. 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.
1 Week Ago #24

twinnyfo
Expert Mod 100+
P: 2,443
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:
Expand|Select|Wrap|Line Numbers
  1. WHERE [FieldName] Like 'Criteria*'
1 Week Ago #25

Rabbit
Expert Mod 10K+
P: 12,230
This is what I was talking about earlier.
Expand|Select|Wrap|Line Numbers
  1. [dbo_Staff List].Manager)=[Forms]![FRM_MainPage]![CB_ManagerFilter]
You can't use an equals with wildcards. They only work with the LIKE operator.
1 Week Ago #26

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. 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?
1 Week Ago #27

NeoPa
Expert Mod 15k+
P: 30,869
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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT Blah, blah, blah " _
  2.        & "FROM   [YourTable]
  3.        & "WHERE  ([Manager] Like '%V')"
  4. strSQL = Replace(strSQL, "%V", Me.cboManagerFilter)
Assuming the code is associated with the Form itself and the ComboBox control is called [cboManagerFilter].
1 Week Ago #28

P: 15
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.
1 Week Ago #29

PhilOfWalton
Expert 100+
P: 1,202
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
Expand|Select|Wrap|Line Numbers
  1. Sub CB_ManagerFilter_AfterUpdate
  2.  
  3.     Dim Fltr as String
  4.  
  5.     With Me.SubformName.Form       ' This is the name of the 
  6.                                    ' Subform on Your Main Form
  7.         If ManagerID = 0 then      ' <All> selected
  8.             .FilterOn = False      ' No Filter
  9.         Else
  10.             Fltr = "ManagerID = " & CB_ManagerFilterCB  ' Combo
  11.             ' Box (The Cound Column)
  12.             .FilterOn = True       ' Apply the Filter
  13.         End If
  14.     End With
  15. End Sub
  16.  
Phil
1 Week Ago #30

NeoPa
Expert Mod 15k+
P: 30,869
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.
1 Week Ago #31

P: 15
Thanks guys.. going to tackle it this weekend.

I will let you know how it works out.
6 Days Ago #32

NeoPa
Expert Mod 15k+
P: 30,869
8-)
Good luck :-)
5 Days Ago #33

P: 15
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.
5 Days Ago #34

NeoPa
Expert Mod 15k+
P: 30,869
Glad to hear it's all working well now.

Cheers.
4 Days Ago #35

Post your reply

Sign in to post your reply or Sign up for a free account.