?
Solved

Custom ORDER BY in MS Access

Posted on 2009-04-01
18
Medium Priority
?
1,436 Views
Last Modified: 2013-11-28
Hello, it is possible to customize the order by from a query? I have one table in MS Access 2007 with a text field. I want to order the result form the query in following way:

1. Field with suffix "_HH"
2. Field with suffix "_H"
3. Field with suffix "_L"
4. Field with suffix "_LL"
5. Other suffix (alt. order by ASC)

Thank you!

SELECT tblVariable.TagName FROM tblVariable
ORDER BY tblVariable.TagName ASC

Open in new window

0
Comment
Question by:tselectro
  • 8
  • 7
  • 3
18 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24041922
Try uising a CASE statement in the order by clause:

Order By
     CASE
          WHEN right(FieldName, 2) = 'HH' then 1
          WHEN right(FieldName, 2) = '_H' then 2
          WHEN right(FieldName, 2) = 'LL' then 3
          WHEN right(FieldName, 2) = '_L' then 4
          Else 5
     END

0
 
LVL 28

Expert Comment

by:TextReport
ID: 24041978
I would use the SWITCH function like this

SELECT tblVariable.TagName FROM tblVariable
ORDER BY Switch(Right(Suffix,2)="HH",1,Right(Suffix,2)="_H",2Right(Suffix,2)="_L",3,Right(Suffix,2)="LL",5,True,5)

Cheers, Andrew

PS Access doesn't support the SQL CASE statement
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24042035
How about:

ORDER BY Mid(fldStr,InStrRev(fldStr,"_")),fldStr

MNelson831: Does A2007 handle Case?  Unless it's been changed in A2007, when you provide an integer in the ORDER BY field, Jet picks up the field in the SELECT clause indicated by the number.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:MNelson831
ID: 24042080
mea culpa - I jumped here from the SQL Server page and didn't realize the question was in Access.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24042090
To correct Andrew's:

SELECT fldText FROM tblName ORDER BY Switch(Right(fldText,3)="_HH",1,Right(fldText,2)="_H",2Right(fldText,2)="_L",3,Right(fldText,3)="_LL",4,True,5) & fldText;

0
 
LVL 15

Accepted Solution

by:
MNelson831 earned 750 total points
ID: 24042132
You could also build a seperate table that look slike this this:

TextData                  SortOrder
_HH                             1
_H                               2
_LL                              3
_L                               4


Then left join that table like this:

Select * from MyRegularTable left outer join MySortTable
on Right(MyFieldName, len(MySortTable.TextData)) = MySortTable.TextData
order by nz(MySortTable.SortOrder,99)



0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 750 total points
ID: 24042177
To correct it and to fix the ones missed also by GRayL

SELECT tblVariable.TagName FROM tblVariable
ORDER BY Switch(Right(Suffix,2)="HH",1,Right(Suffix,2)="_H",2,Right(Suffix,2)="_L",3,Right(Suffix,2)="LL",4,True,5)

I think that covers them all.

Cheers, Andrew
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24042264
Andrew:  Pls run this simple query in A2007

SELECT anyFld FROM anyTable ORDER BY 1;

using your own field and table names.  Does that work, ie. order by anyFld?

0
 
LVL 44

Expert Comment

by:GRayL
ID: 24042472
tselectro:  Did you try my solution at http:#a24042090 ??
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24042501
Andrew, I don't have A2007.  Does the use of an integer in the ORDER BY clause call up the relative field from the SELECT clause?

Ray
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24042571
GRayL there is an error in the SWITCH(), it's missing a comma after the 2.
Also please note the requested order of HH, _H, _L and LL this is not a standard sort order.
Finall Yes ORDER BY 1 works in Access 2007 as it did in previous versions.
Cheers, Andrew
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24042703
Then you realize you were awarded a solution where the asker did not test it.  Your solution would have tried to call up a field, depending on how the Switch() evaluated.  If it rendered a value higher than the number of fields, it will throw an error.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24042842
GRayL, I dont understand what you are getting at. I fixed the issue in the posting that was accepted as an assisted solution. The Switch will carry out 5 tests, the first 4 against the value of the last 2 characters of the field and the 5th being the ELSE as the 9th parameter is True and will return 5.

As to whether it was tested by tselectro then I don't know, as far as I can tell the assisted solution works.

Cheers, Andrew
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24043018
your query:

SELECT tblVariable.TagName FROM tblVariable
ORDER BY Switch(Right(Suffix,2)="HH",1,Right(Suffix,2)="_H",2,Right(Suffix,2)="_L",3,Right(Suffix,2)="LL",4,True,5)

Calls up a field named Suffix ?? in the Switch() function.  However Switch() evaluates, one value of 1 thru 5 per record, will result in Jet asking for the first through fifth field from the SELECT clause - for each record.  As you only listed one field, as soon as Switch() evaluates to 2 or higher, it will generate an error - if not before during compile.

That is why I ampersanded the field name to the result of the switch function.  Sorry I copied your missing comma but them's the breaks.  Understand now?

0
 
LVL 28

Expert Comment

by:TextReport
ID: 24043142
Suffix is the name of the field, it is checking the RIGHT most 2 characters of the field then same way the CASE example whould have done in SQL Server it is not referencing additional fields, it is just a flat form ofthe nesting of IIF's

I think you may be mixing up SWITCH() in VBA with CHOOSE() in VBA

Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24043169
GRayL, just realised what you are getting at with the ORDER BY 2 sorry. No it is a value that will be sorted as a number 2 rather than the column location. Bit slow of me realising what you mean, one eye on the footie.
Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24046995
Hi GRayL, a bit more refreshed after too much celebrating of the footie. I have double checked this and as I thought as soon as you put an expression in the ORDER BY you loose the ability to ORDER BY column position for that specific ORDER BY column. This is in Access and as far as I am aware is the same in all other SQL languages that support ORDER BY 1

ORDER BY 1,2 will sort by the first column then the second
ORDER BY 1, IIf(IsNull([Field2]),2,1) will sort sort by the first column then the NOT NULL Field2 Records followed by the NULL Field2 records
ORDER BY IIf(IsNull([Field2]),1,2) DESC, 1 will sort sort by the NOT NULL Field2 Records followed by the NULL Field2 records then the first column.

I hope this clarifies the situation for everyone with regards to the ORDER BY column location.

Cheers, Andrew
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24051485
Thanks
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question