Sorting items numerically

[cprav]

Hi everyone!

I am creating a Records Management database.
I want it to sort the file ID numberically ( so it goes from 1-99999 and not
1,10,100,2,20...)
My File ID is set up like this: 99999-99 (and sometimes-99; and sometimes-99)
So I am having problems because of the dashes - it will only be text. I did
try a decimal point which worked fine for the ID that only had two sets of
numbers, but not for those that had three or four sets of numbers.

I did contemplate splitting each set into a different field, but did not
like that idea and I also wanted this field to be unique, so that would not
work anyway.

Is there some way to have it stay as a text field, but still sort numerically?

Any suggestions?

Thanks!

 

[Rick Brandt]

Hi everyone!

I am creating a Records Management database.
I want it to sort the file ID numberically ( so it goes from 1-99999
and not 1,10,100,2,20...)
My File ID is set up like this: 99999-99 (and sometimes-99; and
sometimes-99) So I am having problems because of the dashes - it will
only be text. I did try a decimal point which worked fine for the ID
that only had two sets of numbers, but not for those that had three
or four sets of numbers.

I did contemplate splitting each set into a different field, but did
not
like that idea and I also wanted this field to be unique, so that
would not work anyway.

Is there some way to have it stay as a text field, but still sort
numerically?

Well, if you only care about sorting numerically on the part prior to the
firsyt dash you can sort on Val(ID). If you want to sort numerically on all
of the "pieces" then you will have to use expressions to break the data up
into individual sections so that might make an argument for storing them
that way in the first place.

 

[cprav]

That's great! Thanks! .....
Now how do I do that? (make expressions to break the data up - I think I
will try that)

If I store them seperatly, the fields would not be unique anymore
(If I have 99999-99, 99999-80, and 11111-80, breaking them up would be that
no value would be unique anymore). I have a subform linked to this

(sorry, I'm a wiz at Access unless it involves expressions or coding or
something like that!)

 

[Douglas J. Steele]

When creating a primary key, you can have up to 10 separate fields. If you
break the data into two separate fields, then make the combination of those
2 fields your primary key, it would be no different than what you have
today.

On the other hand, you could easily write a couple of functions that'll give
you the first part and the second part:

Function FirstPart(CombinedNumber As String) As Long
FirstPart = Split(CombinedNumber, "-")(0)
End Function

Function SecondPart(CombinedNumber As String) As Long
SecondPart = Split(CombinedNumber, "-")(1)
End Function

You could then add two computed fields to your query that use those
functions, and then sort on those computed fields.

 

[cprav]

Thanks guys!

When creating a primary key, you can have up to 10 separate fields. If you
break the data into two separate fields, then make the combination of those
2 fields your primary key, it would be no different than what you have
today.

On the other hand, you could easily write a couple of functions that'll give
you the first part and the second part:

Function FirstPart(CombinedNumber As String) As Long
FirstPart = Split(CombinedNumber, "-")(0)
End Function

Function SecondPart(CombinedNumber As String) As Long
SecondPart = Split(CombinedNumber, "-")(1)
End Function

You could then add two computed fields to your query that use those
functions, and then sort on those computed fields.