ORDER BY help needed

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

--
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
'================================================= ===


cinnie wrote:
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

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


"John Spencer" wrote:

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
'================================================= ===


cinnie wrote:
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

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


"John Spencer" wrote:

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
'================================================= ===


cinnie wrote:
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

cinnie wrote:
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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com