VBA Code to Sort problem !

[keith01]

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

[CJ_London]

Looks like a query rather than vba code

and you haven’t said what the problem is- wrong result? An error? Something else?

What is the ‘green’ code

show some example data and the result required from that example data

[Welshgasman]

Would be worth posting the link to where you crossposted and got that advice from?
Then we could actually see the 'green' code?

[keith01]

Would be worth posting the link to where you crossposted and got that advice from?
Then we could actually see the 'green' code?

Good Evening,

Apologies for the confusion, here is a copy of the post:-

"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 LLEFT 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 RON L.rownum+1 = R.rownum
WHERE ((([L].[rownum] Mod 2)=1))) AS XINNER 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. "

The Trouble i am having is changinf the join part to sort using a command button.....

Again Apologies for the confusion

Regards

Keith

[RayMilhon]

Good Evening,

Apologies for the confusion, here is a copy of the post:-

"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 LLEFT 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 RON L.rownum+1 = R.rownum
WHERE ((([L].[rownum] Mod 2)=1))) AS XINNER 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. "

The Trouble i am having is changinf the join part to sort using a command button.....

Again Apologies for the confusion

Regards

Keith

Maybe I'm reading this wrong but if you're just talking about a sort then at the end of the query add Order By and the fields you want sorted Such as
Order by L.field1, L.field2, R.field3

[keith01]

The Problem I am Having is that the form is split into two, using the query. So what I am looking to do is add a command button that sorts L.Regn, then R.Regn. (the form is continuous) which is what is causing the problem.

I Attach the DB, which may explain things better than i can !!

Regards

Keith

[Welshgasman]

Add another field as A or B and sort on that then rownum.?

I have to ask, as that query is so complicated to me why did you not ask the person who supplied you that code?

Could perhaps even use a union query here.
Get the evens, then union with the odds?, still need an identying field though.

[xps35]

You also posted here: http://www.vbaexpress.com/forum/show...-Sort-problem-!

There you say: 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"
Here you say: what I am looking to do is add a command button that sorts L.Regn, then R.Regn

So...........?????????

[keith01]

Hi Peter,

That is correct, i am ultimately looking to add a sort for the type in the future, if i could just get the syntax right to sort the registration, then i can adapt the code to sort into type order in the future, it's just a case of seeing how the registration sort works and then i can adapt to sort on other fields.

Hi Welshgasman,

I am not quite sure how a union query would work, would this not cancel out the exisitng query which is generating the form ?

my son did ask the person who supplied the code (from access programmers, some moths back https://www.access-programmers.co.uk.../#post-1819933)

Regards

Keith

[Welshgasman]

That is yet another crosspost.
I was thinking of the one where the advice was given?

[Welshgasman]

Well as that code came from @CJ_London, hopefully he might see this thread.
@keith01, I am not reporting the crosspost at the vbaexpress forum this time. They take crossposting very seriously over there when it is not mentioned, so please be aware in future.

[CJ_London]

The code I provided over at AWF does what was required at that time. This seems to require something different - I don't understand the actual requirement. given the structure of the query and the suggestion I made a year ago and and the one made in post #5 here does not work for you then I suspect what you want to do is not possible. As requested in post #2 show some example data, the result you get at the moment and the result required from that example data.

it may be you need to modify not just the A.myTablePK>= B.myTablePK in both sections, but also the GROUP BY A.myTablePK

The Trouble i am having is changinf the join part to sort using a command button.....

what command button? I don't see it in your attachment. And what code have you tried so far?

[keith01]

Good Morning,
I have been trying to modify the query as per CJLondon’s suggestions without success….

When Query is modified as per post #4 :-
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.Regn>= B.Regn 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.Regn>= B.Regn 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;

This generated the following error:-
“The SELECT Statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.”
Because of the error, access will not let me save the changes to the query


When Query is modified as per post #12 :-
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.Regn>= B.ID WHERE True GROUP BY A.Regn) AS L LEFT JOIN (SELECT A.Regn, Count(B.Regn) AS rownum FROM Tbl_Images AS A INNER JOIN Tbl_Images AS B ON A.Regn>= B.ID WHERE True GROUP BY A.Regn) 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;

This generated the following error:-
“Your Query does not include the specified expression “ID” as part of an aggregate function”

I then modified this query, replacing every instance of “ID” with “Regn” (The field I wish to sort on) :-
SELECT L.*, R.*,
FROM ((SELECT L.Regn AS leftRec, R.Regn AS rightRec FROM (SELECT A.Regn, Count(B.ID) AS rownum FROM Tbl_Images AS A INNER JOIN Tbl_Images AS B ON A.Regn>= B.ID WHERE True GROUP BY A.Regn) AS L LEFT JOIN (SELECT A.Regn, Count(B.Regn) AS rownum FROM Tbl_Images AS A INNER JOIN Tbl_Images AS B ON A.Regn>= B.ID WHERE True GROUP BY A.Regn) 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.Regn;

This Generated the following error:-
“Type Mismatch in expression”

I attach the modified dB, showing the command button “option1”, with the above query, as I say option #4 would not allow me to save without modifying the error, which I was unable to do.

Regards

Keith

[Welshgasman]

Type mismatch should be easy to fix. That is when you indicate a number is text or vice versa by using/not using single quotes '
It could refer to dates also which should be enclosed with #, but rare.

[keith01]

Hi Welshgasman,

I am not seeing any quotes in the query, I think that this is going to be to difficult to accomplish, so i might be better off reverting to a single continuous form until either myself or my son have gained a better understanding of access !!

Thank you all for your help

Regards

Keith