SQL Query ORDER BY Sort Order (Variable)

[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!

 

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

 

[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

 

[TRB_NV]

The UGLY work around works just great!!

Thanks

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

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!