Forum Discussion

AmedMesa's avatar
AmedMesa
Copper Contributor
Oct 23, 2019
Solved

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

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 tblCategor...
access
  • AmedMesa's avatar
    AmedMesa
    Oct 25, 2019

    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.

testmuts's avatar
testmuts
Brass Contributor
Oct 24, 2019

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. 

AmedMesa's avatar
AmedMesa
Copper Contributor
Oct 25, 2019

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.

Resources