I am rewriting a Word VBA routine to use queries stored in an Access .mdb rather than sending the query itself as the CommandText. However, I have run into a snag.
One of my queries has an ORDER BY clause that is set based on user responses in a dialog; there are four possible combinations (involving date/time and text and numeric filds), and the possibility of not using an ORDER BY clause at all.
I am having a lot of fun with parameters, but there does not appear to be a way to use a parameter as the ORDER BY criterion without creating a new field or expression, which seems to be more processing trouble than it would save.
So my options seem to be: (1) create 5 versions of the query in the database; or (2) derive a new query dynamically at run time. The first probably better preserves the advantage of faster execution and simpler code. Is there any reason to explore the second option? If I were going to go that way, how would I retrieve the query to append the ORDER BY clause? Would there be any advantage to writing it back to the database?
All comments appreciated.