SOLVED
Home

Combo box with "All" added to query and sorted on top.

%3CLINGO-SUB%20id%3D%22lingo-sub-937226%22%20slang%3D%22en-US%22%3ECombo%20box%20with%20%22All%22%20added%20to%20query%20and%20sorted%20on%20top.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-937226%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20category%20list%20that%20will%20be%20added%20to%20a%20combo%20box.%26nbsp%3B%20Here%20is%20my%20SQL....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20tblCategoryList.Category%3C%2FP%3E%3CP%3EFROM%20tblCategoryList%3C%2FP%3E%3CP%3EUNION%3C%2FP%3E%3CP%3ESELECT%20%22All%22%3C%2FP%3E%3CP%3EFROM%20tblCategoryList%3CBR%20%2F%3EORDER%20BY%20tblCategoryList.Category%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20code%20works%2C%20my%20problem%20is%20that%20I%20want%20the%20added%20text%20%22All%22%20to%20be%20placed%20on%20top%20of%20the%20list%20produced.%26nbsp%3B%20When%20there%20is%20a%20category%20that%20contains%20special%20characters%20it%20is%20placed%20on%20top%20of%20%22All%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-937226%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-945890%22%20slang%3D%22en-US%22%3ERe%3A%20Combo%20box%20with%20%22All%22%20added%20to%20query%20and%20sorted%20on%20top.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-945890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F432063%22%20target%3D%22_blank%22%3E%40AmedMesa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20make%20an%20extrta%20column%20in%20the%20query%20and%20skip%20it%20in%20the%20combobox.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20tblCategoryList.id%2C%20tblCategoryList.Category%3C%2FP%3E%3CP%3EFROM%20tblCategoryList%3C%2FP%3E%3CP%3EUNION%3C%2FP%3E%3CP%3ESELECT%200%2C%20%22All%22%3C%2FP%3E%3CP%3EFROM%20tblCategoryList%3CBR%20%2F%3EORDER%20BY%20tblCategoryList.id%2C%20tblCategoryList.Category%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMarcel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-945975%22%20slang%3D%22en-US%22%3ERe%3A%20Combo%20box%20with%20%22All%22%20added%20to%20query%20and%20sorted%20on%20top.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-945975%22%20slang%3D%22en-US%22%3E%3CP%3ETry%20%22%20All%22%20instead%20of%20%22All%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20agree%20that%20you%20may%20have%20a%20problem%20here.%20If%20the%20table%20called%20%22tblCategoryList%22%20has%20a%20single%20field%20called%20%22Category%22%2C%20then%20the%20current%20design%20is%20appropriate.%20If%2C%20on%20the%20other%20hand%2C%20%22tblCategoryList%22%20has%20an%20ID%20field%20for%20its%20Primary%20Key%20(using%20the%20AutoNumber)%2C%20then%20that%20is%20the%20appropriate%20field%20to%20be%20used%20in%20the%20combo%20box%20as%20the%20bound%20column%2C%20while%20the%20value%20field%2C%20%22Category%22%20would%20only%20be%20displayed%20to%20users.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-946581%22%20slang%3D%22en-US%22%3ERe%3A%20Combo%20box%20with%20%22All%22%20added%20to%20query%20and%20sorted%20on%20top.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-946581%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381533%22%20target%3D%22_blank%22%3E%40testmuts%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20I%20posted%20wrong%20SQL%20before.%20Needs%20to%20sort%20better%20%3A))%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20iif(tblCategoryList.id%3D0%2C0%2C%221%22%26amp%3B%20tblCategoryList.Category)%20as%20zCategory%2C%20tblCategoryList.Category%3C%2FP%3E%3CP%3EFROM%20tblCategoryList%3C%2FP%3E%3CP%3EUNION%20SELECT%200%2C%22all%22%3C%2FP%3E%3CP%3EFROM%20tblCategoryList%3CBR%20%2F%3EORDER%20BY%20zcategory%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20this%20you%20can%20skip%20the%20first%20column%20and%20it%20will%20work%20as%20needed.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-956058%22%20slang%3D%22en-US%22%3ERe%3A%20Combo%20box%20with%20%22All%22%20added%20to%20query%20and%20sorted%20on%20top.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-956058%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20guys%20for%20helping%20me%20solve%20my%20issue.%20Here%20is%20my%20working%20code%3A%3CBR%20%2F%3E%3CBR%20%2F%3E'%20SELECT%20tblCategoryList.Category%20As%20Category%2C%20%221%22%20As%20ID%3CBR%20%2F%3E'%20FROM%20tblCategoryList%3CBR%20%2F%3E'%20UNION%3CBR%20%2F%3E'%20SELECT%20%22All%20Tags%22%2C%200%3CBR%20%2F%3E'%20FROM%20tblCategoryList%3CBR%20%2F%3E'%20ORDER%20BY%20ID%2C%20Category%3B%3CBR%20%2F%3E%3CBR%20%2F%3EBasically%20I%20created%20a%20row%20in%20SQL%20called%20ID%20and%20all%20records%20added%20from%20the%20tblCategoryList%20table%20are%20labeled%201%20under%20ID.%20The%20Union%20adds%20the%20%22All%20Tags%22%20and%20under%20the%20ID%20column%20the%20row%20is%20identified%20with%200.%20Then%20I%20sort%20via%20ID%20and%20followed%20by%20Category.%20Now%20I%20can%20keep%20the%20order%20ascending%20and%20no%20matter%20what%20the%20%22All%20Tags%22%20remains%20on%20top.%3C%2FP%3E%3C%2FLINGO-BODY%3E
AmedMesa
New Contributor

I have a category list that will be added to a combo box.  Here is my SQL....

 

SELECT tblCategoryList.Category

FROM tblCategoryList

UNION

SELECT "All"

FROM tblCategoryList
ORDER BY tblCategoryList.Category;

 

So the code works, my problem is that I want the added text "All" to be placed on top of the list produced.  When there is a category that contains special characters it is placed on top of "All".

4 Replies

@AmedMesa 

 

I would make an extrta column in the query and skip it in the combobox. 

 

SELECT tblCategoryList.id, tblCategoryList.Category

FROM tblCategoryList

UNION

SELECT 0, "All"

FROM tblCategoryList
ORDER BY tblCategoryList.id, tblCategoryList.Category;

 

Greetings,

 

Marcel

Try " All" instead of "All"

 

However, I agree that you may have a problem here. If the table called "tblCategoryList" has a single field called "Category", then the current design is appropriate. If, on the other hand, "tblCategoryList" has an ID field for its Primary Key (using the AutoNumber), then that is the appropriate field to be used in the combo box as the bound column, while the value field, "Category" would only be displayed to users.

@testmuts 

 

Sorry I posted wrong SQL before. Needs to sort better :)) 

 

SELECT iif(tblCategoryList.id=0,0,"1"& tblCategoryList.Category) as zCategory, tblCategoryList.Category

FROM tblCategoryList

UNION SELECT 0,"all"

FROM tblCategoryList
ORDER BY zcategory

 

With this you can skip the first column and it will work as needed. 

Solution

Thanks guys for helping me solve my issue. Here is my working code:

' SELECT tblCategoryList.Category As Category, "1" As ID
' FROM tblCategoryList
' UNION
' SELECT "All Tags", 0
' FROM tblCategoryList
' ORDER BY ID, Category;

Basically I created a row in SQL called ID and all records added from the tblCategoryList table are labeled 1 under ID. The Union adds the "All Tags" and under the ID column the row is identified with 0. Then I sort via ID and followed by Category. Now I can keep the order ascending and no matter what the "All Tags" remains on top.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies