If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
ORDER BY help needed
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 |
#3
|
|||
|
|||
ORDER BY help needed
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 |
#4
|
|||
|
|||
ORDER BY help needed
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 |
#5
|
|||
|
|||
ORDER BY help needed
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 |
Thread Tools | |
Display Modes | |
|
|