SQL Query ORDER BY Sort Order (Variable)

T

TRB_NV

Would someone please help me correct the syntax for the following Query?

SELECT DISTINCTROW [Docket].[ID], [Docket].[DUEDATE], [Docket].[DOKPREFIX],
[Docket].[DOKSUFFIX], [Docket].[CLIENT], [Docket].[TITLE_SHRT],
[Docket].[SERIAL_NO], [Docket].[PATENT_NO], [Docket].[ITEM]
FROM Docket
WHERE ((([Docket].[DUEDATE]) Between [forms]![Summary]![Start Date] And
[forms]![Summary]![End Date]))
ORDER BY [Docket].[DUEDATE] [forms]![Summary]![Sort Order];

[forms]![Summary]![Sort Order] is populated from a drop down in FORM:Summary
and is either ASC or DESC. I want to give the user the option to select the
sort method when they enter the search criteria.

I've been able to get it to work manually using:
ORDER BY [Docket].[DUEDATE] ASC
ORDER BY [Docket].[DUEDATE] DESC

Thanks!
 
J

John Spencer (MVP)

You can't do it this way.

BUT you might TRY using an Immediate if statement and multiplying the DueDate by
1 or -1. This should work since Dates are stored as numbers. This technique
would not work with strings/text.

ORDER BY [Docket].[DUEDATE] * IIF([forms]![Summary]![Sort Order] = "Desc"),-1,1)
 
M

Michel Walsh

Hi,


You can't supply a parameter where a keyword is expected.

An ugly work around can be:


ORDER BY iif( forms!Summary![Sort Order] = 'ASC', 1, -1)*Docket.DUEDATE ASC


since, for numerical data,
ORDER BY x ASC
and
ORDER BY -x DESC
is similar.



Hoping it may help,
Vanderghast, Access MVP
 
T

TRB_NV

The UGLY work around works just great!!

Thanks

Michel Walsh said:
Hi,


You can't supply a parameter where a keyword is expected.

An ugly work around can be:


ORDER BY iif( forms!Summary![Sort Order] = 'ASC', 1, -1)*Docket.DUEDATE ASC


since, for numerical data,
ORDER BY x ASC
and
ORDER BY -x DESC
is similar.



Hoping it may help,
Vanderghast, Access MVP


TRB_NV said:
Would someone please help me correct the syntax for the following Query?

SELECT DISTINCTROW [Docket].[ID], [Docket].[DUEDATE], [Docket].[DOKPREFIX],
[Docket].[DOKSUFFIX], [Docket].[CLIENT], [Docket].[TITLE_SHRT],
[Docket].[SERIAL_NO], [Docket].[PATENT_NO], [Docket].[ITEM]
FROM Docket
WHERE ((([Docket].[DUEDATE]) Between [forms]![Summary]![Start Date] And
[forms]![Summary]![End Date]))
ORDER BY [Docket].[DUEDATE] [forms]![Summary]![Sort Order];

[forms]![Summary]![Sort Order] is populated from a drop down in FORM:Summary
and is either ASC or DESC. I want to give the user the option to select the
sort method when they enter the search criteria.

I've been able to get it to work manually using:
ORDER BY [Docket].[DUEDATE] ASC
ORDER BY [Docket].[DUEDATE] DESC

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top