Is thisfield sorted

[AlexanderBB]

I thought this would be easy, but I'm bogged down and making it more complicated than it is (I think!)

Using VBA, I need to know if a field in a recordset is correctly sorted alphabetically, or not.

But also if all values are the same so no sort applies.

Any suggestions ?

Thanks

 

[Micron]

Not sure what you mean. You can check the sort order of a dynaset or snapshot recordset (not table based or forward-only recordsets) with the recordset.sort property. The default sort order of a recordset is ascending, so 'alphabetically' is not the term I'd use without qualifying as ascending or descending. As for whether or not it is "properly" sorted, that can depend on your data type and what you expect. For instance, numbers as strings (text) make for poor sorting. The only way you'd know is to visually examine the record values, which you ought to do in query testing.

If all the values are the same, I'm wondering if this is not a problem. Regardless, the default sort still applies, so to say no sort applies is not accurate. It is still being applied, but just doesn't make any difference to the order of records, unless there are other fields in the recordset. Perhaps if you need more than that, a bit more explanation would help.

 

[AlexanderBB]

Hi Micron,

Let's say you want to determine if a string field in a recordset is alpha sorted descending. What might be the optimum way to do this?
I read up a bit on recordset.sort but didn't see anything to suggest it would do this.

 

[Micron]

Here's the thing: it's ascending if you dont' specify (default) in the query. It's faster to apply the sort in the underlying query, which I would always do rather than depend on the recordset sort. Because of this, I've never used the sort property of a recordset - not even would I rely on the table sort. This is because the table is a representation of records in memory, and there is no guarantee that they will appear in your recordset in table order. In fact, it's my understanding that you cannot sort the initial recordset - you have to make another recordset from it an apply the sort to that. I hope you can see why it's far better to create a recordset from a sorted query rather than try to sort a recordset or rely on the order as seen in a table.

To specifically answer your question, I would insert a message box in code to tell you what the applied sort is (assuming this is temporary), but I can't say if you can return the sort order on the primary recordset since (as noted) I don't do things this way. Something like
If rsYourRecordset.Sort <>"" Then msgbox "Sort is " & .Sort
If you want to cover all the bases, use an IFF statement and supply a value for when there is no applied sort. Again, this may only work on a sub-recordset of the primary recordset and I would not even worry about what you're trying to do based on the information I have. Hope that helps.

 

[AlexanderBB]

The recordset is the result of a sorted query on say Field1, based on the table. So the order is set but I then
want to know what order that leaves Field2 in.

I've been trying all kinds of things, but they end up complex and contrived. I keep thinking there must be a 'right' way that's more elegant.

 

[Micron]

So, you're really asking about situations with more than one field! Make a small table (tblSorted) like this:

fruit	animal
apple	rabbit
apple	fox
apple	elephant
orange	aardvark
grapefruit	whale
lemon	lion

<tbody>
</tbody>

and two queries (call 1st qrySorted, the other qryNotSorted) and run them.
qryNotSorted: SELECT tblSorting.fruit, tblSorting.animal FROM tblSorting;
qrySorted: SELECT tblSorting.fruit, tblSorting.animal FROM tblSorting ORDER BY tblSorting.fruit;

Note that with no sort applied, the 1st query should look exactly like your table.
The second is only sorted on the first field, but MAGICALLY the second field will be sorted ascending also. Don't know why, but that's how it works in my version.
See what happens when you apply a descending order on the second field in qrySorted (leave the ascending order on the 1st field)...

So I think that answers the question, but I don't know what you mean by a more elegant way. I don't see how it could be any simpler than setting the sort in the query - on every field if you have to.

 

[AlexanderBB]

Hi Micron, yes I get the same thing here and kindof follow it, but I apply it to what I was trying to do.
I guess I'll bale out, as it's hard to explain further. But basically I copied both a sorted field and unknown field to arrays then used Join to compare them.
Thanks for helping

 

[Micron]

At this point, I still have no clear idea of what you're doing as your posts are somewhat sketchy. Sorry I couldn't have been more help.