Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Use Field As Sort Criteria When Already Being Used As Filter Criteria

Posted by: wheeledgoat Sep 6 2019, 08:55 AM

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.

Posted by: projecttoday Sep 6 2019, 09:27 AM

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.)

Posted by: wheeledgoat Sep 6 2019, 09:29 AM

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.

Posted by: wheeledgoat Sep 6 2019, 09:33 AM

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

Posted by: projecttoday Sep 6 2019, 09:40 AM

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