Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Removing non-number suffix in a field

Status
Not open for further replies.
Feb 19, 2006
28
GB
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 :)
 
What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Is it sufficient to present the string as:

Left(String,Len(String)-1)

Where Val(String) > 0 and Not IsNumeric(Right(String,1))
 
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...
 
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.
 
Use Val and add the leading zeros back in.
 
Here's the solution programmed on a form button:

Code:
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?
 
This is a modified FAQ that may help.
Code:
'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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top