Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    58

    Unanswered: Sort Query with option group

    How can I sort a query by a specific field from a form using an option group. The option group will have a number of radio buttons matching the field names of the query, when an button is selected the query will automatically be sorted by that particular field.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Quote Originally Posted by HilcrRWise
    How can I sort a query by a specific field from a form using an option group. The option group will have a number of radio buttons matching the field names of the query, when an button is selected the query will automatically be sorted by that particular field.
    You can make a SELECT CASE on the Option group, storing the result in a hidden text box, which will be used in your query like that

    SELECT * From yourTable ORDER BY Forms!YourFormName.yourHiddenTextBox

  3. #3
    Join Date
    Jan 2004
    Posts
    58
    I tried that but it doesn't seem to be affecting the query.

    The SQL for the query is:

    SELECT BaseDataSQL_qry.StuRecId, BaseDataSQL_qry.StuReference, BaseDataSQL_qry.StuSurname, BaseDataSQL_qry.StuFirstName, BaseDataSQL_qry.StuSex, BaseDataSQL_qry.ClsDesc, OMNIS_fYearGroup.YrgDesc, OMNIS_fHouse.HseDesc
    FROM (BaseDataSQL_qry INNER JOIN OMNIS_fYearGroup ON BaseDataSQL_qry.StuYrgRecID=OMNIS_fYearGroup.YrgRe cID) INNER JOIN OMNIS_fHouse ON BaseDataSQL_qry.StuHseRecID=OMNIS_fHouse.HseRecID
    WHERE (((BaseDataSQL_qry.StuSex) Like forms!MainForm_frm!Combo58 & "*") And ((BaseDataSQL_qry.ClsDesc) Like forms!MainForm_frm!Combo42 & "*") And ((OMNIS_fYearGroup.YrgDesc) Like forms!MainForm_frm!Combo63 & "*") And ((OMNIS_fHouse.HseDesc) Like forms!MainForm_frm!Combo53 & "*")) Or (((forms!MainForm_frm!Combo58) Is Null) And ((forms!MainForm_frm!Combo42) Is Null) And ((forms!MainForm_frm!Combo63) Is Null) And ((forms!MainForm_frm!Combo53) Is Null))
    ORDER BY Forms!MainForm_frm!Text138;


    The code entered in the hidden text box is:

    =IIf([frame131]=1,"BaseDataSQL_qry.StuSurname, BaseDataSQL_qry.StuFirstName",IIf([frame131]=2,"BaseDataSQL_qry.ClsDesc, BaseDataSQL_qry.StuSurname, BaseDataSQL_qry.StuFirstName"))


    Can you tell where I am going wrong with this.

    Thanks

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Here is an example hope it helps
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Example in Acce$$ 97 version

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •