Solved Table/Query not sorting correctly

[mrbrockert]

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

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

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.

Any insight would be greatly appreciated.

Thanks,

Mark

 

[theDBguy]

Hi. Welcome to AWF!

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

 

[mrbrockert]

Hi. Welcome to AWF!

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

Yes I have

 

[mrbrockert]

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]

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]

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]

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]

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.