RE: Advanced Filter/Sort for Mixed Number and Letter Variables

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.
--
Michael Conroy
Stamford, CT


"Mark P" wrote:

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!

"Michael Conroy" wrote:

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.
--
Michael Conroy
Stamford, CT


"Mark P" wrote:

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!
.

Relevant Pages

  • Re: Text format
    ... "Frank Kabel" wrote: ... > the question was if the lower case letters are ALWAYS ... That is the following string is not in ...
    (microsoft.public.excel.misc)
  • Re: Text format
    ... the question was if the lower case letters are ALWAYS ... That is the following string is not in ... character 10 who have the ascii code>=97. ...
    (microsoft.public.excel.misc)
  • Re: adding a text string to data from one field in one database to another
    ... function that blocks anything but numbers or letters. ... Dim stAllowedChars as string ... dim stFrom as string stTo as string ... dim iPtr as integer. ...
    (comp.databases.ms-access)
  • Re: Format text not all caps
    ... PROPER - Straight out of *Help on this function* when you look at the function ... Converts all other letters to ... =PROPERProper case of first string ... The excel file that I ...
    (microsoft.public.excel)
  • Re: Generate Number on MS. Access
    ... with 10 digits with the combination of 5 Letters from the 26 letters ... Optional NumericOnly As Boolean = False) As String ... Dim l As Long, b As Byte ... Oops, I see this is ten alpha or numerics, but not five of each as you ...
    (comp.databases.ms-access)