Search code examples
sortingms-accesstext

Sorting short text field in MS Access

I am using an Access table from 2010, which holds info about orders in a factory. One of the fields is a job number.

As a new order is entered into a form a job number is manually assigned, in this case the next number up. When the form is accessed by anyone the most current order is displayed and from there you can scroll in descending order.

The problem is we have recently went from 99999 to 100000, now 99999 is always displayed as the most current order and 100000 etc., are at the bottom.

I don't know a way to fix this.

I tried changing the job number field from short text to numeric but I get an error.

Solution

  • You can follow these steps:

    • Add a new field of data type Long.
    • Run an update query setting that field to: VAL([JobNumber])
    • Rename JobNumber to JobNumberText
    • Rename the new field to JobNumber

    Modify the database as needed to accept Long for Short Text