UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Use Field As Sort Criteria When Already Being Used As Filter Criteria, Access 2016    
 
   
wheeledgoat
post Sep 6 2019, 08:55 AM
Post#1



Posts: 67
Joined: 18-December 18



1. I've got a text field that is currently part of the query's filter criteria, works fine.

2. I'd like to use that field to sort one text value to be last in the results (a different value than is being filtered in #1).

Sounds simple, but I don't even know how to get started! Add the field as another column in the query... and then what? As weak as my SQL is, I'm wondering if it isn't the easier approach instead of the GUI query builder.

--------------------
"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

"Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden

Virtue is sufficient for happiness, therefore a sage is immune to misfortune.
Go to the top of the page
 
projecttoday
post Sep 6 2019, 09:27 AM
Post#2


UtterAccess VIP
Posts: 11,069
Joined: 10-February 04
From: South Charleston, WV


Yes, you sort of need a new column. But you cannot sort on an alias. So the Iif has to go in the ORDER BY too. So you don't really need the column itself:
ORDER BY Iif(testfield='testvalue',2,1)
This will put all the records with testfield equal to testvalue after all the other records in the result. (If testfield is numeric, omit the ''s.)

--------------------
Robert Crouser
Go to the top of the page
 
wheeledgoat
post Sep 6 2019, 09:29 AM
Post#3



Posts: 67
Joined: 18-December 18



No idea if this is the best approach, but I added this as a query column and sorted it asc to put all "pump" values at the bottom. seems to work.
CODE
IsPump: IIf([treatment]="pump",1,0)

This might be a total hack approach though, so follow this at your own peril. I actually have no idea what I'm doing.

--------------------
"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

"Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden

Virtue is sufficient for happiness, therefore a sage is immune to misfortune.
Go to the top of the page
 
wheeledgoat
post Sep 6 2019, 09:33 AM
Post#4



Posts: 67
Joined: 18-December 18



Hey, alright. At least I'm not too far off base. Thanks!!!

--------------------
"If you don't make mistakes, you're not working on hard enough problems." -Frank Wilczek

"Success is getting what you want. Happiness is wanting what you get." -B.R. Hayden

Virtue is sufficient for happiness, therefore a sage is immune to misfortune.
Go to the top of the page
 
projecttoday
post Sep 6 2019, 09:40 AM
Post#5


UtterAccess VIP
Posts: 11,069
Joined: 10-February 04
From: South Charleston, WV


Yes, if you look at your SQL code it should say
SELECT ... IIf([treatment]="pump",1,0) AS IsPump
but
ORDER BY IsPump
doesn't work. So you have to
ORDER BY IIf([treatment]="pump",1,0)
which means you can leave out the column in the SELECT clause if you want to.
thumbup.gif

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 11:22 AM