Removing non-number suffix in a field

[shanef1981]

Hey Folks

I'm trying to find a function or formula that will present me with only the numeric part of a field, say if a field has the value of 0475878X, i just want the number without the 'X' suffix.

Problem - The string isn't a fixed length

Problem - There may be more than one letter suffix

Problem - Any leading zero's that do exist must be preserved

Also, on some rare occasions, the value supplied has no numbers at all i.e "Not Applicable", "Not Entered" etc.
These do not need to be kept intact.

The only way I can think of dealing with this is to think of some code that will go through each value in this field and look at the last character, erasing it if it's non-numeric.

One key thing - the letters are always a suffix, so this should help...

Thanks

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Is it sufficient to present the string as:

Left(String,Len(String)-1)

Where Val(String) > 0 and Not IsNumeric(Right(String,1))

 

[shanef1981]

To be honest I haven't tried anything - I was hoping someone out there may have already addressed this and save me hours of experimenting with code as I am unfamiliar with writing modules for use in queries...

 

[shanef1981]

No Remou, looking at that it wouldn't work as sometimes there may be more than one letter suffixed.

One way I would have looked at doing this in the past is using a form and loading each record through it in turn, altering the field with code as each record passed through.

That I could probably fathom out, but totally unsure on how to convert that into a module for use with a query.

 

[Remou]

Use Val and add the leading zeros back in.

 

[shanef1981]

Here's the solution programmed on a form button:

Private Sub ProcessNow_Click()
DoCmd.GoToRecord , , acFirst
While entry_type <> ""
    stock_no.SetFocus
    While Right(stock_no, 1) > "9"
    stock_no = Left(stock_no, Len(stock_no) - 1)
    Wend
    While Right(stock_no, 1) = " "
    stock_no = Left(stock_no, Len(stock_no) - 1)
    Wend
    While Right(stock_no, 1) > "9"
    stock_no = Left(stock_no, Len(stock_no) - 1)
    Wend
    DoCmd.GoToRecord , , acNext
Wend
End Sub

Now I can put that same code on the Form Open handler, to execute when the form is opened.

Is there a tidier way of doing this?

 

[MajP]

This is a modified FAQ that may help.

'How to Strip unwanted chars from a string
'faq705 -3933
'Posted: 24 Jul 03

' --------------------------------------------------
' Function StripString()
'
' Returns a string of specified chars.
' --------------------------------------------------
Function StripString(MyStr As Variant) As Variant
    On Error GoTo StripStringError

    Dim strChar As String, strHoldString As String
    Dim i As Integer

    ' Exit if the passed value is null.
    If IsNull(MyStr) Then Exit Function

    ' Exit if the passed value is not a string.
    If VarType(MyStr) <> 8 Then Exit Function

    ' Check each value for invalid characters.
    For i = 1 To Len(MyStr)
    strChar = Mid$(MyStr, i, 1)
        Select Case strChar
            Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0
                strHoldString = strHoldString & strChar  ' Do nothing
          End Select
    Next i

    ' Pass back corrected string.
    StripString = strHoldString

StripStringEnd:
         Exit Function

StripStringError:
         MsgBox Error$
         Resume StripStringEnd

End Function

You can use the function in a query.

Some Tests:
?stripString("1234xxx")
1234
?stripString("0001234xx")
0001234
?stripString("00A0B 1234xx")
0001234