recordset.sort not working? (1 Viewer)

gbil · Sep 18, 2013

hello.

i am trying to sort a recordset but it is not working:

Set rst = db.OpenRecordset(strSQL)
rst.Sort = "CountOftPrimaryCrop Ascending" 'ASC has same effect

rst.MoveFirst

Do Until rst.EOF
Debug.Print rst.Fields("CountOftPrimaryCrop")
rst.MoveNext

immediate window:

7
1
1

i tried sort descending, immediate window:

7
1
1

what could be wrong?

namliam · Sep 18, 2013

Why not include the order by in your sql statement?
Its faster more efficient etc...

In order to sort your recordset you have to create a second recordset to effectuate the sort... something along the lines of

Code:

Set rst = db.OpenRecordset(strSQL)
rst.Sort = "CountOftPrimaryCrop Ascending" 'ASC has same effect
Set rst2 = rst.Openrecordset

rst2.MoveFirst

Do Until rst2.EOF
    Debug.Print rst2.Fields("CountOftPrimaryCrop")
    rst2.MoveNext

gbil · Sep 18, 2013

rst2.sort is also not working.

Why not include the order by in your sql statement?

here is my sql:

strSQL = "SELECT qryMembers.tPrimaryCrop, Count(qryMembers.tPrimaryCrop) AS CountOftPrimaryCrop"
strSQL = strSQL & " FROM qryMembers"
strSQL = strSQL & " GROUP BY qryMembers.tPrimaryCrop, qryMembers.tOrgCode"
strSQL = strSQL & " HAVING (((qryMembers.tPrimaryCrop) Is Not Null) AND ((Count(qryMembers.tPrimaryCrop)) Is Not Null) AND ((qryMembers.tOrgCode)= '" & strVariable & " ')) ;"

and it won't let me add ORDER BY CountOftPrimaryCrop.

if ORDER BY can be inserted, where in the syntax?

i am just learning as i go.

namliam · Sep 18, 2013

in the query CountoftPrimaryCrop does not yet exist, it is actually created.
Instead you need to order by your Count() that becomes the column

Just like you cannot do "Having CountOftPrimaryCrop is not null" but instead have ((Count(qryMembers.tPrimaryCrop)) Is Not Null)

Also you may want to consider the group by part of your query... which here isnt really a problem since tOrgCode is your where clause thus will be unique... however in "theory" in any field in the group by should also be in the select because of its importance to the row.

gbil · Sep 18, 2013

Hmmn... the query as is, is running ok

So recordset.sort is bugged.

Thanks.

namliam · Sep 18, 2013

No recordset.sort is not bugged, you are using it wrong...
from one of my previous posts....

Code:

Set rst = db.OpenRecordset(strSQL)
rst.Sort = "CountOftPrimaryCrop Ascending" 'ASC has same effect
[U][B]Set rst2[/B][/U] = rst.Openrecordset

[U][B]rst2[/B][/U].MoveFirst

Do Until [U][B]rst2[/B][/U].EOF
    Debug.Print [B][U]rst2[/U][/B].Fields("CountOftPrimaryCrop")
    [B][U]rst2[/U][/B].MoveNext

If you do it that way it works just fine.... inefficient but fine.

gbil · Sep 18, 2013

No recordset.sort is not bugged, you are using it wrong...

you are right. the code now execute as it should be.

and i clean up the query as you suggested.

If you do it that way it works just fine.... inefficient but fine.

can u suggest what i need to improve to make the code more efficient?

even though it will only process some 50 records, i really want to improve my coding (even though i am also the one who will mostly use the final application

)

again, thank you namlian

namliam · Sep 19, 2013

namliam said:
Why not include the order by in your sql statement?
Its faster more efficient etc...

Like I said in my first post, for it to be (most) efficient you should do your order by in your sql

Which I think you had at some point or did you revert to your sorting in code or are you doing it twice now? :banghead:

gbil · Sep 19, 2013

Ooops. i need to delete recordset.sort?

lol.

i already sorted the query. thanks.

recordset.sort not working? (1 Viewer)

gbil

Registered User.

namliam

The Mailman - AWF VIP

gbil

Registered User.

namliam

The Mailman - AWF VIP

gbil

Registered User.

namliam

The Mailman - AWF VIP

gbil

Registered User.

namliam

The Mailman - AWF VIP

gbil

Registered User.

Similar threads

Users who are viewing this thread