Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005

    Unanswered: Looking for and removing alpha charecters

    Here is another question for either using an update query, or using VB code to look for and update the following -

    I will have a field that will contain trailer numbers. Some trailer numbers are all numeric, some are alphanumeric. Examples are -


    However, the 3rd party system that sends us CSV updates also has instances where it puts a 4 digit alpha string in front of the trailer number for their purposes. Example would be -


    What I need to do is, query the table in this field to look for any data where the first 4 characters on the left begin as alpha characters. If that is the case, I want to trim off the 4 alpha characters, leaving only the remaining data. Example -

    EMPU123456 updated to 123456

    SCNNTA123456 to TA123456

    Any ideas of which way is best, and what the code or function would look like? I have done some things in the past using VB code, but in those instances I was working with a set number of digits and now I am not.


  2. #2
    Join Date
    Jan 2005
    Try this:

    Public Function RemoveText(GetText As String) As String
    Dim CheckChr, Counter As Long
    Counter = 1
    Do While Counter < 5
    CheckChr = Asc(Mid(GetText, Counter, 1))
        Select Case CheckChr
        Case 48 To 57 ' digits 0 - 9
        RemoveText = GetText
        Exit Function
        Case Else 'everthing else including special characters
        End Select
    Counter = Counter + 1
      RemoveText = Mid(GetText, 5, Len(GetText))
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts