Will not sort in numerical order

[Gina]

My database has records sorted numerical in the following format:

2008-001, 2008-002, etc.

I have now reached the number 2008-1000 but it is not following 2008-999.
It files the number after 2008-100. I've tried the A-Z ascending sort, but
it does not move the record to the end. Does anyone out there know how I can
fix this?

Thank you.
GM

 

[scubadiver]

Access treats numbers as text differently to numbers as numbers!

I think an easier thing to do would be to put the two numbers in separate
fields and treat the second number as numerical then concatenate them into a
single field.

 

[BruceM]

Your database does not sort the records; rather, that is done in a query or
some other system for ordering records. Whatever the system, it looks as if
you are attempting to sort a text field. Assuming 2008 is the year and you
have a date field in the record, in query design view add something like the
following at the top of a blank column:
MyYear: Year([MyDateField])

Create a number field just for the incremented suffix (001, 002, ...999,
1000, etc.).

In query design view, sort first by MyYear, then by the number field.
Combine the two fields as needed:

=[MyYear] & "-" & IIf([NumberField] <
999,Format([NumberField],"000"),[NumberField])

 

[KARL DEWEY]

The problem is that 2008-001, 2008-002, etc. are not numbers. They are text
and 2008-1000 is less than 2008-999. It sorts left to right so 2008-1 is
less than 2008-9.
You need to change you dash three digit ones to dash four digits.
Backup your database and add a calculated field Len([YourField]) with
criteria of 8.
Run an update query like this --
Replace([YourField], "-", "-0")

 

[Arvin Meyer [MVP]]

Because you are using text, not a number. If you will have less than 10,000
records you need a format like:

2008-0001, 2008-0002

You can fix that with an update query (untested):

UPDATE MyTable SET [MyField] = Left([MyField],5) & "0" & Right([MyField],3)
WHERE ((Mid([MyField],6,1)="0"));

Make sure you test that on a copy of your database first.