Advanced Filter/Sort for Mixed Number and Letter Variables

[Mark P]

I am trying to sort variables by numeric order with Advanced Filter/Sort.
What should I put in the "field" cell to do this? The variables have
different lengths, most (but not all) starting with a letter (or several
letters) then having four (sometimes three) numbers, then sometimes followed
with another letter. For example:

XXX304
I0068
Q5890X
BRI9890A

Thanks in advance!

 

[Michael Conroy]

Your question left me unclear. Are you saying you want to extract the
numerical values from these strings and then sort by those numbers? So with
your sample, you want 304, 68, 5890, and 1989 extracted and sorted by their
numerical value? I am not sure this can be done as you want, but I am not an
expert. My question to you is this, how did this string get created? If the
numbers and letters were seperated at one time, hold on to that information.
Perhaps you should change the form where this information is entered and make
three fields, a prefix, value, and sufix and put them together for your use,
but keep track of the value / number seperately.

 

[Mark P]

I'm sorry about any confusion. I'm kind of new to this! Yes, I do want to
extract the numerical values and then sort by them. That's a simpler way to
say it.

The strings were not created separately, but are part of a coding system
that I am trying to organize. I suppose I could divide them, but all the ones
I need have already been entered into the form, so I'd rather avoid
backtracking if I can avoid it.

Thanks!

 

[John W. Vinson]

The strings were not created separately, but are part of a coding system
that I am trying to organize. I suppose I could divide them, but all the ones
I need have already been entered into the form, so I'd rather avoid
backtracking if I can avoid it.

You're seeing why relational database principles are against storing multiple
facts in one field.

If the text means one thing, and the number means something else - as is
evidently the case, since you're trying to sort by the number independent of
the text - then you really should have *two fields*, not one. It's much easier
to concatenate them for display purposes than it is to extract a portion of
the field.

Just for one thing - you cannot index a portion of a field, so your sort will
get very slow as your database grows; not only will there be no index on the
value you're sorting, but you'll need to call some sort of code in *every
single record in the table* in order to do the sorting.

Stop now, while there's still time - and make this into two or three fields,
one for each logical "chunk" of the code.

 

[Michael Conroy]

Mark,
There is a way to do this and I am working on the formulas. The basics of it
are this, using the ASCII code, numbers are asssigned to letters,
charactures, and numbers. So zero is 48, one is 49, etc. up to nine = 58. If
you run a query using ASC() you will get the ASCII code number for each,
something like this

One: Asc(Mid([Junk],1,1)) and Two: Asc(Mid([Junk],2,1)), etc to eight columns

In a second query use the formula below

Num1: IIf(([One]>47) And ([One]<59),Chr([One]),"")

This checks the range and if valid, takes the ASCII value and turns it back
into a number. You need a seperate formula for about eight columns in both
queries. Then in a final query, just put them together

Final = Num1 + Num2+ ... Num8

Then using Val, you can change the string of numerals into an actual value
RealNum: Val(Final)
I am running into a problem where I have eight columns but the source is
only six charactures long and I am getting an #Error. I know there is an
IsError function but I have not figured out where to implement it. Hope this
helps.

 

[John W. Vinson]

Mark,
There is a way to do this and I am working on the formulas. The basics of it
are this, using the ASCII code, numbers are asssigned to letters,
charactures, and numbers. So zero is 48, one is 49, etc. up to nine = 58. If
you run a query using ASC() you will get the ASCII code number for each,
something like this

One: Asc(Mid([Junk],1,1)) and Two: Asc(Mid([Junk],2,1)), etc to eight columns

In a second query use the formula below

Num1: IIf(([One]>47) And ([One]<59),Chr([One]),"")

This checks the range and if valid, takes the ASCII value and turns it back
into a number. You need a seperate formula for about eight columns in both
queries. Then in a final query, just put them together

Final = Num1 + Num2+ ... Num8

Then using Val, you can change the string of numerals into an actual value
RealNum: Val(Final)
I am running into a problem where I have eight columns but the source is
only six charactures long and I am getting an #Error. I know there is an
IsError function but I have not figured out where to implement it. Hope this
helps.

Well... that's CERTAINLY not going to work. That will take the number 123, get
(by a long complex roundabout route) the values Num1 = 1, Num2 = 2 and Num3 =
3 and give a Final value of 6... just as you would get for 222 or 321.

There's a Val function that *does all this for you*. If you insist (unwisely,
again!) on using this composite field, you could use

Public Function ExtractNum(strIn As String) As Long
Dim iPos As Integer
ExtractNum = 0
If Len(strIn) > 0 Then
For iPos = 1 to Len(strIn)
If IsNumeric(Mid(strIn, iPos) Then
ExtractNum = Val(Mid(strIn, iPos))
Exit Function
End If
End If
End Function

 

[Mark P]

I apologize in advance if this is a dumb question. Now, how do I use the code
in a sorting way? I know with the advanced filter/sort tool you can input
some very short code, but I don't know how well it would work with this.
Would it be easier to make another column in my table that transformed the
data I want to sort, using this code? If so, how? Thanks for your help and
patience!

 

[raskew via AccessMonster.com]

Hi -

Here's a possible example:

x = "XXX304"
? val(x)
0
? getnumer2(x)

304
x = "123"
? getnumer2(x)
123

Public Function GetNumer2(ByVal pStr As String) As Currency
'*******************************************
'Purpose: Returns the first numerical
' sequence in a string
'Coded by: raskew
'Inputs: ? getNumer2("ABC123")
'Output: 123
'*******************************************
Dim n As Integer
Dim strHold As String
Dim strKeep As String

strHold = Trim(pStr)
n = Len(strHold)
Do While n > 0
If val(strHold) > 0 Then
strKeep = val(strHold)
n = 0
Else
strHold = Mid(strHold, 2)
n = n - 1
End If
Loop

GetNumer2 = val(strKeep)

End Function

Bob

 

[Michael Conroy]

Mark,
Reading back over your posts, particularly where you say you are new to
this, I think the answers you are being given may be beyond your experience
level. Both solutions will work but you are still asking how they fit into an
advanced filter. They don't. You use a bunch of queries for mine and you put
John's code into a module and put the results into a new column in the table.
If you are lost here then let us know and we can address that issue.

This ugly text string can never be sorted by the numbers unless the numbers
are seperated from the text into a new column.

First off, you have not given a good reason why this column needs to be
sorted. How important is this information to your database and why, what do
the numbers and letters mean? No sane database builder would let this string
get created and then try to use it for sorting purposes.

Secondly, you have not explained how the string is being created. If you
don't know then say so. If you can't modify the creation process, say so. It
is unlikely that someone is typing this in. It looks like it is being created
by code based on the user's selections of various options somewhere.

Thirdly, this mess of text is unusable for any sorting or searching as it
exists. If the letters and numbers are important to your database and you are
adament about keeping this information, then you must split the information
into seperate columns. Add three columns to the table and manually type the
prefix letters into the first column formated as text, manually type the
numbers into the second column formated as number, and manually type the
suffix letters into the third formated as text. These three colomns can later
be combined, called concatenation, and the result will be text and numbers
combined. And to answer your innitial question, then you can do a simple sort
on the middle column, the one with the numbers.

I hope this helps you.

John, the joining of the numbers is not adding the numbers together, the
results are in text format so at that point I am concatenating the results.
The val gets rid of leading zeros and turns the string into a number.
However, I like your code better as it handles the length of the string
better.

 

[Mark P]

The coding refers to different materials where I work. Why they are organized
in such a fashion, I have no idea. I can't change how they are created. I
will try to split them up and then use concatenation. I was just hoping there
was a faster way to do it, since there's 3500+ items! Thanks.

 

[Michael Conroy]

Mark,
Thanks for clearing it up. So you are stuck with this string. Well take
another look at my earlier answer. The three queries will work. It might be
easier to play with this in Excel. You could export the table and use my
"formulas" in Excel rather than in a query. Each number and letter has an
ACSII code value and you can convert each characture in the string to its
code. Then check that the ASCII value corresponds to a number (>47 and <58)
and convert the code back to a number.

With your example BRI9890A and using ASC() you can get B= 66, R=82, I=73,
9=57, 8=56, 9=57, 0=48, A=65
Then use if(AND(Code Num1>47,Code Num<58),chr(Code Num1),"")
FullString = Concatenate the results Code Num1&CodeNum1&CodeNum3 ...
Then take the value of the concatenation, val(FullString) and turn it into a
number.
It is a bit of a process but it will work.
Good luck.