Access 2016 ListBox not Sorting Properly

[RMittelman]

I have a ListBox on my Access form to find and select a record from the bound table. Here is the ListBox's RowSource:

SELECT SGID, SGGroupName, SGCategory FROM tblSGroups ORDER BY SGGroupName;

If I execute that query in the query window, I get the following result (which I've scrolled a bit to show pertinent records):



When I view the ListBox on the form, I get the following (also scrolled to the right entry):





Note the 2 entries at the end are out of order. I can't figure this out. Why would 2 random records show up out of order?
They ARE the last 2 entries I put into the table, but they should be showing alphabetically, right?

Any ideas? Thanks...

[Welshgasman]

Look for hidden characters.
How did you add those two. Compared against the rest?

[June7]

Try retyping the data for those two records.

[RMittelman]

Thanks for the replies. I can't find any special characters. I went into the underlying table and found the records and re-typed the names. Same thing. Works fine if I execute the query, but not in the ListBox which has the query in the underlying rowsource.

I added the 2 records through an import process where they give me an Excel workbook which I link to as a temporary table, then update the data into the permanent table using VBA. Even if the import process caused this, re-typing the names directly in the table after clearing the fields should have fixed that. So I don't think it's a hidden character. There's something strange about the ListBox that is causing this.

If I can't get this worked out, I think I will have to use a continuous subform instead of the listbox. The oly reason I need this is to quickly jump to the desired record by picking it out of a list.

[Gicu]

Can you please show us the list box in design view with the property box showing the row source and the column number\widths (probably need to take 2 screenshots)?

Cheers,

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Micron]

The problem might be with the other records. If they have a leading space, they will sort first.

[RMittelman]

Can you please show us the list box in design view with the property box showing the row source and the column number\widths (probably need to take 2 screenshots)?

Cheers,

[RMittelman]

The problem might be with the other records. If they have a leading space, they will sort first.

If that were the case, wouldn't they also show in the same wrong order when the query is executed in the query design window?

[RMittelman]

If you want to provide db for analysis, follow instructions at bottom of my post.

Thanks June7. I can take the single table and form and copy them to a new database and submit that. I will do that if I don't soon receive an answer on this thread.

[Gicu]

Can't see anything wrong in the screenshots, but can you please try to save the query that gives you the right results and use that in the row source (just selected from the queries tab when you click the builder on the right) and see what happens?

Cheers,

[Micron]

If that were the case, wouldn't they also show in the same wrong order when the query is executed in the query design window?

I would expect as much, but a query behaving differently than a listbox was the only thing I could think of.

[RMittelman]

Can't see anything wrong in the screenshots, but can you please try to save the query that gives you the right results and use that in the row source (just selected from the queries tab when you click the builder on the right) and see what happens?

Cheers,

I thought that was just what I did. If you click on the ellipsis next to Row Source, it opens the query designer with that query. Running the query there shows the records in the proper order. With no changes at all to the Row Source query text, opening the form in form view shows the records in the wrong order.

[Gicu]

That is not what I meant. In the query designer create a new query with the same SQL statement as the current one in the row source of the listbox. Run it and confirm it returns the properly sorted records. If yes save the query naming it qrySortedList or similar. Now in design view if the form open the properties window for the listbox and in the Row source line simply enter qrySortedList. Save the form and open it in form view and check the sort order.

Cheers,

[RMittelman]

That is not what I meant. In the query designer create a new query with the same SQL statement as the current one in the row source of the listbox. Run it and confirm it returns the properly sorted records. If yes save the query naming it qrySortedList or similar. Now in design view if the form open the properties window for the listbox and in the Row source line simply enter qrySortedList. Save the form and open it in form view and check the sort order.

Cheers,

Well, seems it should work whether you have a saved named query or not. The SQL code in the row source, pasted into the query designer SQL view, returns the records properly sorted. Shouldn't have to save it and use the saved query name. However, I did that and it still didn't work, the 2 problem records are still at the bottom. Definitely seems like something with the ListBox. But thanks for trying...