Need to delete first string value of a field

[jallen919]

I have a column that sometimes contains two string values. I actually only need it to contain one string value. If there are two string values in the field, I will always need to delete the first string value and leave the second one. In addition, the first string value always starts with two and the second string value always starts with nine.

 

[LarryDeLaruelle]

Jallen:

How you implement this depends on how you are manipulating the data (importing, etc).

Basically, you want a function that looks in the string to find the point where the second part begins with the '9' and then extracts only from that point to the right.

The code would look something like this:

Dim intStart as Integer
Dim intLength as Integer

intLength = Len(FieldName)
intStart = InStr(FieldName,'9')
FieldName = Mid(FieldName, intStart, intLength - (intStart - 1)

intLength captures the length of the entire string;
intStart captures the position of the '9' where the second part begins.

The syntax of the Mid function is String Name, Starting Point, Number of Characters. You must subtract 1 from intStart or you will lose the last character of the string.

This should get you going; check help for more info on the functions.

If you can tell me how you need to implement this, I can give you a bit more help in how to do it.

Larry De Laruelle
larry1de@yahoo.com

 

[MichaelRed]

Public Function basScndStr(StrIn As String) As String

    'Function to return the Second Str - If there are MORE than one
    'Actually we will delete the first Str it there is more than one

    Dim MyVar As Variant

    MyVar = Split(StrIn)
    If (UBound(MyVar) = 0) Then
        basScndStr = UBound(MyVar)
    Else
        MyVar(0) = ""
        basScndStr = Trim(Join(MyVar))
    End If
        
End Function

but it will not work with any BUT Access 2K


MichaelRed
redmsp@erols.com

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

 

[MichaelRed]

Should read my own tag line.

Public Function basScndStr(StrIn As String) As String

    'Function to return the Second Str - If there are MORE than one
    'Actually we will delete the first Str it there is more than one

    Dim MyVar As Variant

    MyVar = Split(StrIn)
    If (UBound(MyVar) = 0) Then
        basScndStr = MyVar(UBound(MyVar))
    Else
        MyVar(0) = ""
        basScndStr = Trim(Join(MyVar))
    End If
        
End Function

Caveat re Access 2K only still applies.


MichaelRed
redmsp@erols.com

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

 

[jallen919]

Not to be dense but I'm not sure how to put this code in my DB and run it on the table I am working with... can you help with that too please

 

[MichaelRed]

Do an "UpdateQuery" in the query design grid. Place the "field" in the top row and

"basScndStr([Field])" in the updateto row.
([Field]), of coursr represents the name of the field in the database you want to trim. It is the same as the fieldname in the top row.


MichaelRed
redmsp@erols.com

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

 

[jallen919]

Where do I place the code that you so kindly wrote for me?

 

[jallen919]

OK -- here is what I did... I added a new module and just called the module Functions and inserted the code you wrote. When I run the query I get a message saying RunTime Error 5 -- invalid procedure call or argument and it stops and highlights the line: MyVar = Split(StrIn)

Thanks very much for your help on this.

 

[MichaelRed]

Just paste it inot any 'general' module

That is any "Module" shown in the database window. If there are none, create one and paste the code "anywhere". It is supposed to 'arrange' itself as necessary.


MichaelRed
redmsp@erols.com

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