Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #14438

Re: Strange behaviour when sorting

Show all headers | View raw

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

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Strange behaviour when sorting Keith Tizzard <internet.shopping@foobox.com> - 2022-06-13 09:37 -0700
  Re: Strange behaviour when sorting Ron Weiner <rw@domain.com> - 2022-06-13 16:37 -0400
    Re: Strange behaviour when sorting Keith Tizzard <internet.shopping@foobox.com> - 2022-06-14 06:15 -0700
      Re: Strange behaviour when sorting Keith Tizzard <internet.shopping@foobox.com> - 2022-06-15 07:09 -0700

csiph-web