I have query for a dropdown:
Select CustID, AccountName from Customers

which displays 50884 records

If I sort it:
Select CustID, AccountName from Customers
Order by AccountName

it only displays 12984

and sorting the other way
Select CustID, AccountName from Customers
Order by AccountName Desc

displays the other 37900

Has anyone else come across this strange behaviour? and why does it happen?

Keith Tizzard formulated the question :
> I have query for a dropdown:
> Select CustID, AccountName from Customers
>
> which displays 50884 records
>
> If I sort it:
> Select CustID, AccountName from Customers
> Order by AccountName
>
> it only displays 12984
>
> and sorting the other way
> Select CustID, AccountName from Customers
> Order by AccountName Desc
>
> displays the other 37900
>
> Has anyone else come across this strange behaviour? and why does it happen?

WOW, that is an awful lot of rows for a Combo Box. You might want to
consider another method to whittle down the number of rows you fetch
from the table before populating your Combo, List, etc. Typically when
I have a situation like yours I use a Text Box and a List box in
combination. When the form is opened both controls are empty and as
the user starts typing text into the Text Box I re-query the list's row
source using the fragment of text that they typed. Air code below:

Private Sub txtSearch_Change()
Dim strSql As String

strSql = "Select CustID, AccountName from Customers " _
& "Where LastName like '*" _
& Replace(Nz(txtSearch.Value, ""), "'", "''") " _
& "ORDER BY AccountName;"
lstSearch.RowSource = strSql

End Sub

You might want to consider adding some option buttons to allow the
user to customize the sorting, and perhaps forcing the user to type in
two or three characters before changing the Row Source. Just my $.02

Rdub

I agree that there are many records. I had not realised that. How tables grow over 20 years of use.

However this is not the problem. Nor is it to do with the SQL being used for a dropdown. The problem lies in the query itself.

This has been working perfectly well for 20 years until just the other day. I thought that either an update in Access or some new data could be the problem.

After much experimenting I suspect there must be something in the data but cannot find what it is. If I add an arbitrary condition on another field in the table that excludes no records the sorting works correctly E.g.

SELECT CustomerID, AccountName
FROM Customers
WHERE AccountStatus Is Not Null
ORDER BY AccountName;

The field AccountStatus always has a non null value.

A complete mystery!!

I exported the table to a new database in which it is the only table. And everything works!!

I therefore suspect some data error and will later Compact and Repair the database to see if that resolves it.

Jim
On Monday, 13 June 2022 at 21:37:29 UTC+1, Ron Weiner wrote:
> Keith Tizzard formulated the question :
> > I have query for a dropdown:
> > Select CustID, AccountName from Customers
> >
> > which displays 50884 records
> >
> > If I sort it:
> > Select CustID, AccountName from Customers
> > Order by AccountName
> >
> > it only displays 12984
> >
> > and sorting the other way
> > Select CustID, AccountName from Customers
> > Order by AccountName Desc
> >
> > displays the other 37900
> >
> > Has anyone else come across this strange behaviour? and why does it happen?
> WOW, that is an awful lot of rows for a Combo Box. You might want to
> consider another method to whittle down the number of rows you fetch
> from the table before populating your Combo, List, etc. Typically when
> I have a situation like yours I use a Text Box and a List box in
> combination. When the form is opened both controls are empty and as
> the user starts typing text into the Text Box I re-query the list's row
> source using the fragment of text that they typed. Air code below:
>
> Private Sub txtSearch_Change()
> Dim strSql As String
>
> strSql = "Select CustID, AccountName from Customers " _
> & "Where LastName like '*" _
> & Replace(Nz(txtSearch.Value, ""), "'", "''") " _
> & "ORDER BY AccountName;"
> lstSearch.RowSource = strSql
>
> End Sub
>
> You might want to consider adding some option buttons to allow the
> user to customize the sorting, and perhaps forcing the user to type in
> two or three characters before changing the Row Source. Just my $.02
>
> Rdub

Just to conclude, a Compact and Repair did the trick. I assume that an index had become corrupted

On Tuesday, 14 June 2022 at 14:15:55 UTC+1, Keith Tizzard wrote:
> I agree that there are many records. I had not realised that. How tables grow over 20 years of use.
>
> However this is not the problem. Nor is it to do with the SQL being used for a dropdown. The problem lies in the query itself.
>
> This has been working perfectly well for 20 years until just the other day. I thought that either an update in Access or some new data could be the problem.
>
> After much experimenting I suspect there must be something in the data but cannot find what it is. If I add an arbitrary condition on another field in the table that excludes no records the sorting works correctly E.g.
>
> SELECT CustomerID, AccountName
> FROM Customers
> WHERE AccountStatus Is Not Null
> ORDER BY AccountName;
>
> The field AccountStatus always has a non null value.
>
> A complete mystery!!
>
> I exported the table to a new database in which it is the only table. And everything works!!
>
> I therefore suspect some data error and will later Compact and Repair the database to see if that resolves it.
>
> Jim
> On Monday, 13 June 2022 at 21:37:29 UTC+1, Ron Weiner wrote:
> > Keith Tizzard formulated the question :
> > > I have query for a dropdown:
> > > Select CustID, AccountName from Customers
> > >
> > > which displays 50884 records
> > >
> > > If I sort it:
> > > Select CustID, AccountName from Customers
> > > Order by AccountName
> > >
> > > it only displays 12984
> > >
> > > and sorting the other way
> > > Select CustID, AccountName from Customers
> > > Order by AccountName Desc
> > >
> > > displays the other 37900
> > >
> > > Has anyone else come across this strange behaviour? and why does it happen?
> > WOW, that is an awful lot of rows for a Combo Box. You might want to
> > consider another method to whittle down the number of rows you fetch
> > from the table before populating your Combo, List, etc. Typically when
> > I have a situation like yours I use a Text Box and a List box in
> > combination. When the form is opened both controls are empty and as
> > the user starts typing text into the Text Box I re-query the list's row
> > source using the fragment of text that they typed. Air code below:
> >
> > Private Sub txtSearch_Change()
> > Dim strSql As String
> >
> > strSql = "Select CustID, AccountName from Customers " _
> > & "Where LastName like '*" _
> > & Replace(Nz(txtSearch.Value, ""), "'", "''") " _
> > & "ORDER BY AccountName;"
> > lstSearch.RowSource = strSql
> >
> > End Sub
> >
> > You might want to consider adding some option buttons to allow the
> > user to customize the sorting, and perhaps forcing the user to type in
> > two or three characters before changing the Row Source. Just my $.02
> >
> > Rdub