Sorting of running numbers with Alphabet

[Guest]

I am using Access 2003 and I would like to know how can I have running
sequent when I have running numbers with alphabets behind??

Below is what I have done currently,

“Data Type†is set as Numbers
“Field Size†is Long Integer
“Format†is “Aâ€-000
“Decimal Places†is 0
“Iput Mask†is 9999

With this setting, I can sort all my data in running sequent and without
keying the default value "A". Meaning the data field it would appear as,
A-001, A-002, A-003...........

Now, if I am going to add alphabet behind the running numbers. This setting
would not work. Due to the alphabet would be changing from A to Z.

Example: 001, 001A, 002, 003, 004, 004A, 004B, 004C……..

If I change the “Data Type†to “Textâ€, there is no way I can sort the
running numbers in sequent. Due to the sorting method is different in numbers
and text.

Problem I foresee is how can I set the “Data Type†to sort both contain
running numbers and running numbers with alphabets??

Can anyone help me on this??

 

[Arvin Meyer [MVP]]

Here is a database sample with code that works with Alphanumeric increments
from AA001 through ZZ999:

http://www.datastrat.com/Download/AlphaNumeric2K.zip

You should be able to adjust it to make your numbers work.

 

[Pieter Wijnen]

You can use the Format Function to make sure the number part is always
stored as 3 digits or in queries to coerce it
using a function

Public Function NumSort(ByVal v As Variant, Optional ByVal NumLen As Long =
4) As Variant
Dim nls As String, suff As String
Dim Num As Long, i As Long

If VBA.IsNull(v) Then
NumSort = Null
Exit Function
End If
nls = VBA.String(NumLen, 48)
For i = 1 To VBA.Len(v)
If Not VBA.IsNumeric(VBA.Mid(v, i, 1)) Then
suff = VBA.Mid(v, i)
Exit For
Else
Num = VBA.Val(VBA.Left(v, i))
End If
Next
NumSort = VBA.Format(Num, nls) & suff
End Function

HtH

Pieter

 

[Guest]

Dear Arvin Meyer [MVP],

Thank you very much for your prompt respond.

But I have a problem here. I need to have a "-" between the a starting and
running numbers. Meaning, AA-001, AA-002, AA-003, AA-003A, AA-003B, and so
on......

Also if using Data Type in "Text", then the running numbers would not be in
sequent. Meaning:

AA-1100
AA-11000
AA-11001
AA-11002
AA-11003
AA-11004
AA-11005
AA-11006
AA-11007
AA-11008
AA-11009
AA-1101
AA-11000
AA-11001
and so no.......

From here, you can see that running sequent has jumped. If I used the method
I mention, earlier. Such problem would not occur.

The problem I face now was that I need to add extra alphabet behind the
running numbers.

 

[Pieter Wijnen]

You have to zero pad the number part to get it right
ie
AA-01100
AA-01101
AA-11000
AA-11001

HtH

Pieter

 

[Guest]

I was trying to find a method not to key in the running numbers. I knew that
by entering a extra "0" in front would solve the problem. But if someone
forget to key in the "extra )", then the runnings numbers would be in a mess.
This would cuase duplicate numbers being created. Example, AA-0100 &
AA-100........ Both would the same, only one with an extra "0" in front.This
is what I try to avoid.

Thus, I would like to know if there are any other method??

 

[Pieter Wijnen]

This ought to be usable in a Query to sort your data (or in a BeforeUpdate
Event to Fix the data)
Public Function NumSortPre(ByVal v As Variant, Optional ByVal NumLen As Long
= 5) As Variant
Dim nls As String, pre As String, c As String * 1
Dim Num As Long, i As Long

If VBA.Len(Access.Nz(v, VBA.vbNullString)) = 0 Then
NumSortPre = Null
Exit Function
End If
nls = VBA.String(NumLen, 48)
i = 1
pre = v
While i < VBA.Len(pre) And Not VBA.IsNumeric(VBA.Mid(pre, i, 1))
i = i + 1
Wend
If i = VBA.Len(pre) And Not VBA.IsNumeric(VBA.Mid(pre, i, 1)) Then
NumSortPre = pre
Exit Function
End If
If i > 0 Then
pre = VBA.Left(v, i - 1)
End If
Num = VBA.Val(VBA.Mid(v, i))

NumSortPre = pre & VBA.Format(Num, nls)
End Function

HtH

Pieter

 

[Guest]

This is my thought too, using Query to sort. I try to avoid using it. I am
not good with VBA. Seems like you are good with VBA??

How do I apply what you have written to Query??

 

[Pieter Wijnen]

You paste the code in a new module & save under a different name from
NumSortPre

To Use In A Query:
SELECT NumSortPre([YourField]) From YourTable
ORDER BY NumSortPre([YourField])

HtH

Pieter

 

[Paul Shapiro]

You didn't describe the meaning of the data, but if the numbers should
always sort numerically perhaps that should remain a numeric field. If your
data is a multi-part code of some sort, A-999-AA, can it logically be stored
in 3 attributes, or 2 attributes if the prefix will ALWAYS and FOREVER
remain "A": codePrefix (text), codeNumber (int), codeSuffix (text). Then you
would sort by those 3 attributes without any need to separate the pieces of
the combined text field. If there is any meaning to the prefix and suffix
they should always be stored as separate attributes anyway. Whatever rule
you use to separate the pieces is probably going to be fragile. Wait long
enough and some change will break it. That's why the first normal form of db
design specifies that every attribute value be an atomic, indivisible value.
Paul Shapiro