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

On Tue, 19 Aug 2008 16:43:01 -0700, Michael Conroy
<MichaelConroy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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


--

John W. Vinson [MVP]
.

Relevant Pages

  • Re: AUTOMATIC DATE/VALUE QUESTION...
    ... Do you really need to store the letter? ... computed field to that query to return the letter. ... The Chr function returns the character associated with a specific ASCII code ... figure out how to make the field insert the letter value that corresponds ...
    (microsoft.public.access.forms)
  • Re: number of occurances of letter in a field then multiplied by value
    ... This would be impossible in a Table and rather difficult in a Query ... (and you're asking in the tablesdbdesign newsgroup). ... simple VBA function to Kabbalize words in this manner. ... Dim iPos As Integer ...
    (microsoft.public.access.tablesdbdesign)
  • Find all chars in table that are ASCII code 128 and Greater
    ... Does anyone know how to query a field in a table where it contains an ... ASCII code>= 128 - without looping through every field for every ... Ex of char I would like to find: ü ...
    (comp.databases.ms-sqlserver)