A little help with string manipulation please. (1 Viewer)

[BukHix]

I am trying to write code that looks at a field in a recordset. I want to check a string in the RS to see if it has a hyphen or a numeric character at the end of the string.

If there is a hyphen like this 3493- I want to remove the hypen and leave 3493. If there is a letter like this 4893-A I want it to leave the string alone. Any ideas of how to achieve this? I have this code that I gleamed from another string manipulation that I was doing but I have not been successful in converting it for my current needs.

Dim objDB As Database
Dim objRS As Recordset
Dim AllowedCharacters As String
Dim TempString As String
Dim ctr As Integer

AllowedCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-"

Set objDB = CurrentDb()
Set objRS = objDB.OpenRecordset("JobNumbers1", dbOpenDynaset)
While Not objRS.EOF
    TempString = ""
        For ctr = 1 To Len(objRS![JobNumber])
            If InStr(1, AllowedCharacters, _
                Mid(objRS![JobNumber], ctr, 1)) <> 0 Then _
            TempString = TempString & _
                Mid(objRS![JobNumber], ctr, 1)
        Next
        With objRS
            .edit
            ![JobNumber] = TempString
            .Update
            .MoveNext
        End With
Wend
End Sub

 

[Fizzio]

Can you not use a simple Right() statement to filter for the "-" before processing the rest of the code or using Right() with IsNumeric() to check for a number (I somehow think this is too easy though )

 

[GJT]

I prefer :

Set objDB = CurrentDb()
Set objRS = objDB.OpenRecordset("JobNumbers1", dbOpenDynaset)
While Not objRS.EOF
TempString = objRS![JobNumber]
With objRS
.edit
![JobNumber] = StrConvert(TempString)
.Update
.MoveNext
End With
Wend
End Sub


Private Sub StrConvert(ByRef strVal as string)
' look at last digit of string
Dim tmpStr as string

tmpStr = Right(strVal,1)
If tmpStr = Asc(45) Then ' Ascii 45 = - char
strVal= Left (strVal,Len(strVal-1))
Endif

End Sub

 

[BukHix]

GJT,

I must be missing a step. When I try the code you posted. I am getting an error that says Compile Error: Expected Function or Variable. It then highlights the strConvert
![JobNumber] = strConvert(TempString)

Any ideas what I may of missed?

 

[GJT]

Sorry - my mistake....I didnt test it!

The function should read :

Private Function StrConvert(strVal as string) as String
' look at last digit of string
Dim tmpStr as string

tmpStr = Right(strVal,1)
If tmpStr = Asc(45) Then ' Ascii 45 = - char
strVal= Left (strVal,Len(strVal-1))
Endif
StrConvert= strVal

End Sub

 

[BukHix]

GJT, I am getting close but I get this error now:

Compile Error: Variable required - cant assign to the expression. Debug highlights the Private Function with Yellow and then puts a dark one character highlight over the - (minus) in the strVal = Left(strVal, Len(strVal - 1))

Thanks for all your help so far.

Private Function StrConvert(strVal As String) As String
Dim tmpStr As String
tmpStr = Right(strVal, 1)
If tmpStr = Asc(45) Then
strVal = Left(strVal, Len(strVal - 1))
End If
StrConvert = strVal

End Function

 

[Fizzio]

Should be

strVal= Left (strVal,Len(strVal)-1)

 

[raskew]

You didn't provide an example of how a numeric character (integer)
might appear at the end of your string, so I'll assume it would be
similar to the alpha character example (4893-A), with something
like 4893-9.

If this is the case, try the following from the debug window:

widget = "3493-"
widget = iif(right(widget,1)="-" or isNumeric(right(widget,1)), left(widget, instr(widget, "-")-1), widget)
? widget
3493

widget = "3493-A"
widget = iif(right(widget,1)="-" or isNumeric(right(widget,1)), left(widget, instr(widget, "-")-1), widget)
? widget
3493-A

widget = "3493-9"
widget = iif(right(widget,1)="-" or isNumeric(right(widget,1)), left(widget, instr(widget, "-")-1), widget)
? widget
3493

Is this close?

Bob