Solved Table/Query not sorting correctly (1 Viewer)

mrbrockert

New member
Local time
Today, 09:19
Joined
Mar 25, 2022
Messages
4
Hello,

I have a table and query that will not sort correctly.

I would like to sort the field [JOB#] in descending order and there are 7 records that always move up to the top.
I have included many screen shots to show what is happening.

Below is the Table and Query
Table.JPG
Query.JPG


Below is the query design view showing only [JOB#] is sorted in descending order and no other fields are sorted.
Query1.JPG
Query2.JPG


I included some images below proving the sort function sorts correctly for all other records as I thought maybe the "-C" was the culprit. I also check for leading or trailing spaces, and I tried creating new records altogether and the same sorting error occurs.

Capture.JPG
Capture1.JPG


Any insight would be greatly appreciated.

Thanks,

Mark
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:19
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Have you checked if there are any leading space characters in your data? Just curious...
 

mrbrockert

New member
Local time
Today, 09:19
Joined
Mar 25, 2022
Messages
4
Hi. Welcome to AWF!

Have you checked if there are any leading space characters in your data? Just curious...
So it appears that every record with Job# under 1000 has a leading space, where these 7 did not. So adding the leading space allowed them to sort properly. I am not sure if this was originally on purpose or happened when converting from MS Works.

Thanks for your help!

Mark
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:19
Joined
Oct 29, 2018
Messages
21,358
So it appears that every record with Job# under 1000 has a leading space, where these 7 did not. So adding the leading space allowed them to sort properly. I am not sure if this was originally on purpose or happened when converting from MS Works.

Thanks for your help!

Mark
Glad to hear you got it sorted out. Rather than add leading spaces, wouldn't you prefer to remove all leading spaces instead? Just curious...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2002
Messages
42,981
I would have gone the other way. Having a leading space is very bad.

I'm sure the problem was that the field is text and the job numbers with three numeric characters were not sorting correctly once the sequence number got to be 1000.

At some point, you will get to 5 digits and you have the problem all over again. It really is better to fix the problem so that it won't break again.

Maybe you could remove the prefix and keep it in a separate field so the numeric part of the job number can be used to sort the jobs into ascending order
 

mrbrockert

New member
Local time
Today, 09:19
Joined
Mar 25, 2022
Messages
4
I would have gone the other way. Having a leading space is very bad.

I'm sure the problem was that the field is text and the job numbers with three numeric characters were not sorting correctly once the sequence number got to be 1000.

At some point, you will get to 5 digits and you have the problem all over again. It really is better to fix the problem so that it won't break again.

Maybe you could remove the prefix and keep it in a separate field so the numeric part of the job number can be used to sort the jobs into ascending order
Yes you’re right. I thought about removing the prefix and that is obviously the answer we’re looking for but this would need to be a decision made by my supervisor.

But seeing as 3000 is where we’re at after 30 years since the company’s start in 1992, I imagine it will be left the way it is as these job numbers are used in quick books and with our suppliers.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2002
Messages
42,981
I wasn't suggesting that you get rid of the prefix. I know how fond users are of these things:)

If the letters are always constant, you can keep a concatenated version for search and display and automatically prepend the prefix when you save the number. I hope the field isn't the PK because that removes many options. We all don't like concatenated values since they duplicate data but this isn't terrible. How does the number part get generated? It looks like you also have a suffix. Does the number part repeat?

Or if the string is the PK, then make sure you didn't break any relationships by changing the value to prefix a blank. If the number part is unique, you could add an additional column to the table and add a unique index to it. Use that field to generate the next sequence number and use the number in your queries to sort.
 

Users who are viewing this thread

Top Bottom