How Can We Have Access Accept Order By Input Param

[alee via AccessMonster.com]

Hello,

I want to be able to pass a string as a parameter to Access SQL query engine
within Visual Studio's table adapter editor and have it return a table sorted
by the Order of the string entered:

SELECT CustomerID, CompanyName, ContactName, Country
FROM Customers
ORDER BY ?

I could enter the above query inside Visual Studio.NET 2005 table adapter.
When I tried
to preview the result, Access ignored what I typed in the param textbook.

I tried to enter:

Country ASC
Country DESC
CustomerID ASC
CustomerID DESC

But, none of them seemed to have any effect on the resulting table.

Any help will be greatly appreciated.

Alan L.

 

[Michel Walsh]

Its is probably far from being fast, but if the result has a small number of
records, you can try:


ORDER BY CHOOSE(paramIndex, CustomerID, CompanyName, ContactName, Country)


where paramIndex is a parameter, an integer identifying which item in the
list you want use for the ordering.

It is probably quite slow, since no existing index can be used.



Hoping it may help,
Vanderghast, Access MVP

 

[KARL DEWEY]

UNTESTED ---
Create a separate query for each different sort add an output field like ---
CustomerSort: 1
and
CompanySort: 2

Use criteria --
[Forms]![YourForm]![Option]

Then use a UNION query to pull the queries together. You will get data
only from one query into the union query.

 

[alee via AccessMonster.com]

Hello Karl,

Thanks for your suggestion. I have used the method suggested by Michel in
the previous
thread.

The only question I have left is:

How can I include ASC | DESC in the query without create two separate queries
in
the table adapter.

Any suggestion will be greatly appreciated.

Best regards,

Alan L.

UNTESTED ---
Create a separate query for each different sort add an output field like ---
CustomerSort: 1
and
CompanySort: 2

Use criteria --
[Forms]![YourForm]![Option]

Then use a UNION query to pull the queries together. You will get data
only from one query into the union query.

Hello,

[quoted text clipped - 22 lines]

Alan L.

 

[Michel Walsh]

Doable, but doubly slow:



ORDER BY CHOOSE(param1, 0, field1, fiel2, fiel3) ASC, CHOOSE(param2, 0,
field1, field2, field3) DESC


with param1=1 and param2=2, you have something equivalent to ORDER BY field1
DESC
with param1=2 and param2=1, you have something equivalent to ORDER BY field1
ASC


Hoping it may help,
Vanderghast, Access MVP

Hello Karl,

Thanks for your suggestion. I have used the method suggested by Michel in
the previous
thread.

The only question I have left is:

How can I include ASC | DESC in the query without create two separate
queries
in
the table adapter.

Any suggestion will be greatly appreciated.

Best regards,

Alan L.

UNTESTED ---
Create a separate query for each different sort add an output field
like ---
CustomerSort: 1
and
CompanySort: 2

Use criteria --
[Forms]![YourForm]![Option]

Then use a UNION query to pull the queries together. You will get data
only from one query into the union query.

Hello,

[quoted text clipped - 22 lines]

Alan L.

 

[alee via AccessMonster.com]

Hello Michel,

It works!

Thanks a lot.

Alan L.

Its is probably far from being fast, but if the result has a small number of
records, you can try:

ORDER BY CHOOSE(paramIndex, CustomerID, CompanyName, ContactName, Country)

where paramIndex is a parameter, an integer identifying which item in the
list you want use for the ordering.

It is probably quite slow, since no existing index can be used.

Hoping it may help,
Vanderghast, Access MVP

Hello,

[quoted text clipped - 24 lines]

Alan L.