Query Update Separate String from Number

[eclypz]

Hello,

I need some help...

I have a db, that contains two tables (tblAgents; tbl Service), the tblAgents, have one column with the username, and the username can be something like: XPT001 until XPT400; ZDTR01 until ZDTR25; PPTXR0001 until PPTXR1000.

In this table i want to add a column that contains only the initials of the user in the line, for example: XPT; ZDTR; PPTXR...

I'm having troubles to implement an update query that could help me in this task. In excell i use this: LEFT(A1,MIN( FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1).

But now i need something like this but in access because the quantity of records that i need to work.

can any one help me with this?

thanx,

[Stewart Ross]

Hello,

I need some help...

I have a db, that contains two tables (tblAgents; tbl Service), the tblAgents, have one column with the username, and the username can be something like: XPT001 until XPT400; ZDTR01 until ZDTR25; PPTXR0001 until PPTXR1000.

In this table i want to add a column that contains only the initials of the user in the line, for example: XPT; ZDTR; PPTXR...

I'm having troubles to implement an update query that could help me in this task. In excell i use this: LEFT(A1,MIN( FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1).

But now i need something like this but in access because the quantity of records that i need to work.

can any one help me with this?

thanx,

Hi eclypz. Here is a simple string function which will extract the non-numeric characters from your string and return them. It relies on the ASCII ordering of characters, where 0-9 are lower in code value than all alphabetic characters in the characterset. It won't be quite as fast as the Excel function; if this is an issue I'm sure there are other, faster methods available.

To use it, copy the code below into any general code module in your database, and in whatever query you are building to view the fields of your base tables add a column with a field name referring to the function, like this:

UserInitials: ExtractAlpha([username])

Public Function ExtractAlpha(StringIn As String) As String
    'Extracts and returns the non-numeric characters from a string
    Const AsciiNine = 57
    Dim ChVal As Integer, Ch As String
    Dim Extracted As String, N As Integer, I As Integer
    N = Len(StringIn)
    For I = 1 To N
        Ch = Mid$(StringIn, I, 1)
        ChVal = Asc(Ch)
        If ChVal > AsciiNine Then
            Extracted = Extracted & Ch
        End If
    Next I
    ExtractAlpha = Extracted
End Function
 
 

Regards

Stewart