ORDER BY help needed

[Guest]

Hello to all
Lets say I have a query like this...

SELECT P.CustID, P.CustName, P.A1, P.A2, P.B1, P.B2, ...
FROM ............ AS P
WHERE ...........
ORDER BY P.A1 DESC, P.CustName;

so far so good. Everything works. Now, my form that is based on this query
has a combo box whose value list is A1, A2, B1, B2, .... I want the ORDER BY
to get its first sort citeria from this cbx. Something like

ORDER BY Forms!frmCust.cbxSortCrit DESC, P.CustName;

No luck - the second sort criteria is followed, but the 1st is just ignored.
Does anyone know how I can solve this?

thanks

 

[John Spencer]

You could use the switch function. This could lead to fairly slow query.

ORDER BY
SWITCH (Forms!frmCust.cbxSortCrit="A1",[A1],
Forms!frmCust.cbxSortCrit="A2",[A2],
Forms!frmCust.cbxSortCrit="B1",[B1],
Forms!frmCust.cbxSortCrit="B2",[B2]) DESC, P.CustName





'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

thanks John for your response to my AS and my ORDER BY posts. You indicated
that determining the cbx's values in the ORDER BY line by using a Switch
function has me concerned. Is there a better way to select the column to
sort by that might be quicker?

thanks
--
cinnie

You could use the switch function. This could lead to fairly slow query.

ORDER BY
SWITCH (Forms!frmCust.cbxSortCrit="A1",[A1],
Forms!frmCust.cbxSortCrit="A2",[A2],
Forms!frmCust.cbxSortCrit="B1",[B1],
Forms!frmCust.cbxSortCrit="B2",[B2]) DESC, P.CustName





'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello to all
Lets say I have a query like this...

SELECT P.CustID, P.CustName, P.A1, P.A2, P.B1, P.B2, ...
FROM ............ AS P
WHERE ...........
ORDER BY P.A1 DESC, P.CustName;

so far so good. Everything works. Now, my form that is based on this query
has a combo box whose value list is A1, A2, B1, B2, .... I want the ORDER BY
to get its first sort citeria from this cbx. Something like

ORDER BY Forms!frmCust.cbxSortCrit DESC, P.CustName;

No luck - the second sort criteria is followed, but the 1st is just ignored.
Does anyone know how I can solve this?

thanks

 

[Guest]

Actually, John, your suggestion is working fine with no real time issues.
But, it is sorting the columns as strings, not the numbers they are.

I get 8.65, 5.21, 10.65, 0.23 instead of 10.65, 8.65, 5.21, 0.23

Can I get SWITCH to treat these values as numbers, or do I need to think of
another function.

thanks
--
cinnie

You could use the switch function. This could lead to fairly slow query.

ORDER BY
SWITCH (Forms!frmCust.cbxSortCrit="A1",[A1],
Forms!frmCust.cbxSortCrit="A2",[A2],
Forms!frmCust.cbxSortCrit="B1",[B1],
Forms!frmCust.cbxSortCrit="B2",[B2]) DESC, P.CustName





'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello to all
Lets say I have a query like this...

SELECT P.CustID, P.CustName, P.A1, P.A2, P.B1, P.B2, ...
FROM ............ AS P
WHERE ...........
ORDER BY P.A1 DESC, P.CustName;

so far so good. Everything works. Now, my form that is based on this query
has a combo box whose value list is A1, A2, B1, B2, .... I want the ORDER BY
to get its first sort citeria from this cbx. Something like

ORDER BY Forms!frmCust.cbxSortCrit DESC, P.CustName;

No luck - the second sort criteria is followed, but the 1st is just ignored.
Does anyone know how I can solve this?

thanks

 

[Rick Brandt]

Actually, John, your suggestion is working fine with no real time
issues. But, it is sorting the columns as strings, not the numbers
they are.

I get 8.65, 5.21, 10.65, 0.23 instead of 10.65, 8.65, 5.21, 0.23

Can I get SWITCH to treat these values as numbers, or do I need to
think of another function.

Wrap the Switch function in a Val() function.