All,

Rich P. gave me a solution to this that works in VB, but I am very
curious - can this be done within MS Access in a formless DB?

I have a query I need to ORDER BY by either;
a) The result of another query (always a single text string, e.g;
"TBL_Timers.TimerPos DESC", or;
b) a variable containing that result.

Is there any way for me to do this in MS Access? I was thinking a
nested query might do the trick, but keep poking myself in the eye...

I've gotten as far as verifying I can see my variable (SortStr) in a
query result set (just to prove to myself I'm building it correctly).
It comes back as "TBL_Timers.TimerPos DESC" w/o quotes.

But I can't seem to figure out how to insert (npi) it into my SQL
code.

I thought I could use

SELECT TBL_Timers.TimerID, TBL_Timers.TimerPos
FROM TBL_Timers order by SortStr();

But the results don't sort.

Any suggestions? Am I going about this wrong?

Thanks,

Patrick

On 8 Apr., 03:54, Patrick A wrote:
I expect this not to work because SortStr() is not a field in the
table and if it were its result would be the same for all records.

What you'd like is to put the two strings together:
aQuery= "SELECT TimerID, TimerPos FROM TBL_Timers order by " +
SortStr()
to obtain
aQuery= "SELECT TimerID, TimerPos FROM TBL_Timers order by TimerPos
DESC" which of course is the way of doing this in VBA. No use if you
don't want to work with VBA.

Also: if you have already a variable or a function returning the
"order by" fragment, the variable or the function can return the whole
query just as easily and you can set it as a filter. But it is of no
use if you have no forms nor reports.

You could first run a query which fills a field with the right values:
UPDATE tblTimers SET fldSort = iif(SortStr()="TimerPos",TimerPos,
iif(SortStr="TimerID",TimerID, Null))
and then
SELECT TimerID, TimerPos, fldSort FROM tblTimers order by fldSort
but I cannot think of a way of processing DESC/ASC.

Interesting, maybe someone else has an idea.

Greetings
Marco P

Thanks for trying, Marco.

Anyone else have an idea?

Patrick


On Apr 8, 2:42*pm, Marco Pagliero <mart... (AT) web (DOT) de> wrote: