Results 1 to 4 of 4

Thread: Parsing data

  1. #1
    Join Date
    May 2004
    Posts
    13

    Unanswered: Parsing data

    Hi everyone,

    I have a problem that is puzzling me but I'm sure someone thinks is very easy.

    I have a field name claim_number. The data is similar to this:

    JHC-123-456
    BHC-9395949
    JV90385
    BHWC0494934

    I need the characters from this field; not the "-" nor any of the numbers. Any ideas how to do this in MS Access VB?

    Thanks
    Tina

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    One thing that would make it easier (and faster)...

    Is it always true that anything after a dash is numeric?
    Is it always true that once a number appears in your text, everything
    after it will be a number?
    Inspiration Through Fermentation

  3. #3
    Join Date
    May 2004
    Posts
    13
    Both of those statements are true.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This is an example of how to parse the data...
    This takes care of all your samples, plus any data that may be
    in the form of JHC1-23456

    Code:
    Function parsedata()
    Dim strData As String
    Dim strParsed As String
    Dim intParse As Integer
    Dim intLen As Integer
    'you'll want to pull your data into a recordset here
    'and loop thru one record at a time,  populating strData
    strData = "JHC123456"
    intParse = InStr(1, strData, "-") - 1
    'parse out anything starting with from the dash on
    If intParse > 0 Then
      strParsed = Left$(strData, intParse)
    Else
      strParsed = strData
    End If
    'now step through the string one at a time until you find a number
    For intParse = 1 To Len(strParsed)
      If IsNumeric(Mid$(strParsed, intParse, 1)) Then
        strParsed = Left$(strParsed, intParse - 1)
        Exit For
      End If
    Next
    'you will probably put your new data in another table here
    End Function
    Inspiration Through Fermentation

Posting Permissions

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