strip a field 2

[ktighe]

I need to strip the first couple of letters (it varies) off of the beginning of a field. I have the following code, which should do it. This code is in a module in my database. My problem is, I don't know how to utilize the module in my query. I'm pretty sure that this is easy to do, but I'm very new to Access, so.....
Any help would be greatly appreciated.

sample fields to strip:

original: stripped:
ss-323-saer-23 323-saer-23
316-123-123-123 123-123-123
(I need to strip all characters up to and including the first dash '-')

Function strip(strIn As String) As String

Dim Index As Integer
Dim return_string As String
Dim flag As Boolean

flag = False
Index = 1

Do While Index < Len(strIn)
If Mid(strIn, Index, 1) = &quot;-&quot; Then
If flag = False Then
flag = True
return_string = Mid(strIn, Index + 1, Len(strIn) - Index)

End If
End If
Index = Index + 1
Loop

strip = return_string

End Function

 

[tlbroadbent]

Select strip(colname) From table Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[ktighe]

Terry,

First of all, thanks for the quick response. I'm still having problems, though. I'm trying to do all of this in an Access query. I tried adding the select statement you gave into the sql code in access, but kept getting the error that the method 'strip' was not found. I also tried putting the following into the 'critera' field in the querys design view: strip(fieldname). I tried making my function public, but to no avail. Is there an easier way to do this? Thanks again.

Kevin

 

[tlbroadbent]

1st - The function must defined in the current database.
2nd - Enter the code in a Field Name cell of query designer form. For example: NewName: strip(colname)

You can select other columns in the query also. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;I'm not dumb. I just have a command of thoroughly useless information.&quot; - Calvin, of Calvin and Hobbes

 

[MichaelRed]

I like the split/join functions for these little chores. If you have Access 2K, the following would be my suggestion. Otherwise, Terry's drawing board will have to do for the time being.

Public Function basStrip2Dash(StrIn As String) As String

    'Michael Red     6/26/01

    'Usage/Example:
    '? basStrip2Dash("ss-323-saer-23")
    '323-saer-23

    '? basStrip2Dash("316-123-123-123")
    '123-123-123

    Dim strOut As Variant
    Dim Idx As Integer

    strOut = Split(StrIn, "-")
    For Idx = 1 To UBound(strOut)
        strOut(Idx - 1) = strOut(Idx)
    Next Idx
    ReDim Preserve strOut(UBound(strOut) - 1)
    basStrip2Dash = Join(strOut, "-")

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ktighe]

It worked! I used Michael's code, but the real problem was that I just didn't know the syntax for using a module function in a query. Thanks for your help everyone.

Kevin

 

[ktighe]

correction: I actually used my original (ugly) code. Michael's code produced an error at the ReDim Preserve... line (subscript out of range), and I didn't feel like debugging.....
Thanks again!

 

[MichaelRed]

ktighe,

Sorry the code producd an error. Reviewing it - after your last post, the only way I can see a problem where you describe it is if the input string does not include the dash (&quot;-&quot separator character.

I realize that you probably won't use it, but the revised routine is provided to take care of this possability:

Public Function basStrip2Dash(StrIn As String) As String

    'Michael Red     6/26/01

    'Usage/Example:
    '? basStrip2Dash("ss-323-saer-23")
    '323-saer-23

    '?basStrip2Dash("316-123-123-123")
    '123-123-123

    Dim strOut As Variant

    strOut = Split(StrIn, "-")
    If (UBound(strOut) > 0) Then
        ReDim Preserve strOut(UBound(strOut) - 1)
        For Idx = 1 To UBound(strOut)
            strOut(Idx - 1) = strOut(Idx)
        Next Idx
    End If
    basStrip2Dash = Join(strOut, "-")

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over