Consulting

Results 1 to 8 of 8

Thread: Sleeper: VBA Code to Sort problem !

  1. #1
    VBAX Newbie
    Joined
    Jun 2023
    Posts
    4
    Location

    Sleeper: VBA Code to Sort problem !

    Dear Sirs,
    I have the following VBA Code, which I wish to Modify:-
    SELECT L.*, R.*
    FROM ((SELECT L.ID AS leftRec, R.ID AS rightRec FROM (SELECT A.ID, Count(B.ID) AS rownum
    FROM Tbl_Images AS A INNER JOIN Tbl_Images AS B ON A.ID>= B.ID WHERE True GROUP BY A.ID)  AS L LEFT JOIN
    (SELECT A.ID, Count(B.ID) AS rownum
    FROM Tbl_Images AS A INNER JOIN Tbl_Images AS B ON A.ID>= B.ID WHERE True GROUP BY A.ID)  AS R ON L.rownum+1 = R.rownum
    WHERE ((([L].[rownum] Mod 2)=1)))  AS X INNER JOIN Tbl_Images AS L ON X.leftRec = L.ID) INNER JOIN Tbl_Images AS R ON X.rightRec = R.ID;
    The Advice I have been given to modify the code is :-
    For sorting, you would need to change the join part A.myTablePK>= B.myTablePK in both the green sections to the field you are sorting on - i.e. to A.fieldName>=B.fieldName for ascending and B.fieldName>=A.fieldName for descending.
    with regards filtering you will need to apply criteria to both the green parts of the above query and not use the form filter functionality - replace True with whatever your filter requirements are - which is good practice anyway. I've included the WHERE True to hopefully make it easier to for you to edit, it is not actually required in this example

    However no matter what combination I try I cannot get either of these modifications to work !

    Any Advice would be much appreciated

    Best Regards

    Keith

  2. #2
    VBAX Tutor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    270
    Location
    That is not VBA code, it is SQL.

    Should provide sample data and desired output. Can build tables in post or attach file.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    VBAX Newbie
    Joined
    Jun 2023
    Posts
    4
    Location
    Quote Originally Posted by June7 View Post
    That is not VBA code, it is SQL.

    Should provide sample data and desired output. Can build tables in post or attach file.
    Thank you very much for your reply June7

    I attach a copy of the DB, what i am trying to achieve is to place a command button on the form that displays the form in either "Regn" Order or type "Order", but because of the underlying query this is proving to be very hard

    Any advice would be much appreciated

    Regards

    Keith
    Attached Files Attached Files

  4. #4
    VBAX Tutor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    270
    Location
    Query Qry_Regn_Order has a popup input for Tbl_ImagesQuery.Reg1. I cannot find this reference in the query design and the referenced query does not exist. I had to recreate query object.

    Why would you have form based on query instead of table?
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    VBAX Newbie
    Joined
    Jun 2023
    Posts
    4
    Location
    Quote Originally Posted by June7 View Post
    Query Qry_Regn_Order has a popup input for Tbl_ImagesQuery.Reg1. I cannot find this reference in the query design and the referenced query does not exist. I had to recreate query object.

    Why would you have form based on query instead of table?
    Hi jUne07,

    The query is to generate two tables for the form ( L & R ), so that i can place two images side by side on the form....

    Regards

    Keith

  6. #6
    VBAX Tutor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    270
    Location
    Okay, got it, should have recognized that.

    Check out this query that has same output.

    SELECT L.*, R.* FROM Tbl_Images AS L INNER JOIN (SELECT *, ID-1 AS RID FROM Tbl_Images) AS R ON L.ID=R.RID WHERE L.ID Mod 2=1;

    Is this a class exercise? Was that advice provided by instructor? Why would you change JOIN clause to change sort order? Any field in query can be used to sort on. Why not sort or filter on form?

    I can't see 'green parts' in query.

    If you want to have 2 options on form to change sort order, probably need a Toggle button or Radio buttons in an option group or a Combobox, not a single Command button. Which one appeals to you?
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    VBAX Newbie
    Joined
    Jun 2023
    Posts
    4
    Location
    Quote Originally Posted by June7 View Post
    Okay, got it, should have recognized that.

    Check out this query that has same output.

    SELECT L.*, R.* FROM Tbl_Images AS L INNER JOIN (SELECT *, ID-1 AS RID FROM Tbl_Images) AS R ON L.ID=R.RID WHERE L.ID Mod 2=1;

    Is this a class exercise? Was that advice provided by instructor? Why would you change JOIN clause to change sort order? Any field in query can be used to sort on. Why not sort or filter on form?

    I can't see 'green parts' in query.

    If you want to have 2 options on form to change sort order, probably need a Toggle button or Radio buttons in an option group or a Combobox, not a single Command button. Which one appeals to you?
    Good Afternoon June01,

    The sql originated from a post on utter access, where my son originally asked how he could present two record's side by side on a form. The Sql was provided by a contributor, and the display side of things works perfectly. However sorting the form in a meaningful manner was impossible ( ie sorted left to right on "Regn" continuously. Here is the code and the reply :-

    " you can do it if you can relate the records in some way so you can display both on one row. This example uses two non standard joins and assumes you are using a numeric PK. You will end up with fields from two records in the same row - in this example each field is designated as belonging to L or R tables.

    SELECT L.*, R.*
    FROM ((SELECT L.myTablePK AS leftRec, R.myTablePK AS rightRec
    FROM

    (SELECT A.myTablePK, Count(B.myTablePK) AS rownum
    FROM myTable AS A INNER JOIN myTable AS B ON A.myTablePK>= B.myTablePK WHERE True GROUP BY A.myTablePK) AS L
    LEFT JOIN
    (SELECT A.myTablePK, Count(B.myTablePK) AS rownum
    FROM myTable AS A INNER JOIN myTable AS B ON A.myTablePK>= B.myTablePK WHERE True GROUP BY A.myTablePK) AS R
    ON L.rownum+1 = R.rownum
    WHERE ((([L].[rownum] Mod 2)=1))) AS X
    INNER JOIN myTable AS L ON X.leftRec = L.myTablePK) INNER JOIN myTable AS R ON X.rightRec = R.myTablePK;

    the two parts in green are identical and use a non standard join to get a row number.

    These are then joined with a non standard join to a) get the odd counted records as the left record PK and the even numbers as the right record PK (being the left count+1) - identified in blue. Note the left join in case there is not a final even number in the list

    finally the left and right PK's are joined back to the original table in orange. Note the use of aliasing to keep it simple

    This won't be an updateable query and clearly you will need to repeat controls for the left and right sides

    all you should need to do is to change myTable to the name of your table and myTablePK to the name of your primary key.

    with regards filtering you will need to apply criteria to both the green parts of the above query and not use the form filter functionality - replace True with whatever your filter requirements are - which is good practice anyway. I've included the WHERE True to hopefully make it easier to for you to edit, it is not actually required in this example

    For sorting, you would need to change the join part A.myTablePK>= B.myTablePK in both the green sections to the field you are sorting on - i.e. to A.fieldName>=B.fieldName for ascending and B.fieldName>=A.fieldName for descending. "

    Hence my referencing the green part of the code (Sorry for the confusion !). I would like a combo box on the form header, to sort by either regn / type or photo location.

    we have tried all sorts of combinations replacing the code in green,but nothing seems to work.......

    Regards

    Keith

  8. #8
    VBAX Tutor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    270
    Location
    Well, I do not understand that advice.

    What I did:

    1. set form RecordSource to my query
    SELECT L.*, R.* FROM Tbl_Images AS L INNER JOIN (SELECT *, ID-1 AS RID FROM Tbl_Images) AS R ON L.ID=R.RID WHERE L.ID Mod 2=1;

    2. set up a listbox instead of combobox - either can be used
    Name: lstSort
    RowSource: Regn;Type;Photo_Location
    RowSourceType: Value List

    3. code in AfterUpdate event
    Private Sub lstSort_AfterUpdate()
    Me.OrderBy = "L." & Me.lstSort
    Me.OrderByOn = True
    End Sub

    I picked the L fields for sorting on. If you want to allow sort by either L or R fields, modify list and code to accommodate. And if you want to allow DESCENDING as a choice, have another control for user (again, Toggle, Radio, or Combo/List box) and modify code.
    Last edited by June7; 06-21-2023 at 07:43 AM.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Posting Permissions

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