Results 1 to 7 of 7
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    MODIFYING A STRING (A2K SR1)

    A field in a table is identified as WBS and is a text field. The WBS format is inconsistent such that all of the following WBS data records are currently being used:

    C.12.1
    C.12.1.1
    C.12.1.1.5.3.2
    C.1.1.5.3.2
    C.12.10.5.3.2
    C.2.3.2

    I need to convert each WBS record to a consistent format as follows: C.00.00.00.00.00.00 such that the number of characters between dots is always two spaces. (C.12.1.1 would convert to C.12.01.01, C.2.3.2 converts to C.02.03.02) The WBS always starts with C. Any suggestions on how to do this.

    I can think of a brute force method where I can assign assign each character in the string to a variable based on the length of the string and then loop through the data and insert zero's and adjust the data when needed, however, I think there must be a better way.

    Thanks for your suggestions.

    (Doesn't anyone have a neat function that already does this?)
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MODIFYING A STRING (A2K SR1)

    I don't think you have an option, your brute force method seems the way to go.
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MODIFYING A STRING (A2K SR1)

    Gary,

    Last year, Francois wrote an elegant function for you to assist in sorting WBS "numerically" - see <post#=118794>post 118794</post#>. This function can be adapted to do what you want:

    Function Enlarge(txt As String) As String
    Dim ar() As String
    Dim i As Integer
    ' Split string at periods and store parts into array
    ar = Split(txt, ".")
    ' Treat first element separately
    Enlarge = ar(0)
    ' Loop through the remaining elements
    For i = 1 To UBound(ar)
    Enlarge = Enlarge & "." & Format(ar(i), "00")
    Next i
    End Function

    Now, you can create an update query that changes WBS to Enlarge([WBS]). Or, if you don't want to disturb the original data,make a new field and fill it with Enlarge([WBS]).

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: MODIFYING A STRING (A2K SR1)

    Thanks Hans,

    I originally went to the Enlarge function but couldn't figure out how to make it apply to this instance. In fact, I am using the Enlarge function in several applications for sorting purposes and it works great.

    Thanks for making the modifications. I will give it a try later today. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: MODIFYING A STRING (A2K SR1)

    WOW - Works Fantastic. A million thanks.

    If you are ever in Maryland, I will need to buy you one of the many beers I owe you for your help. <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MODIFYING A STRING (A2K SR1)

    Hi Hans
    The Split function, is this an Access function or a special? As usual I cannot find it in the A2k help.
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MODIFYING A STRING (A2K SR1)

    Hello Pat,

    Split is a VB6 function that was included in Access VBA starting with Access 2000, along with several other functions such as InStrRev and Replace. You should be able to find it in the online help in the Visual Basic Editor, not from Access itself. In Access 97, there was no separate Visual Basic Editor, and the Access help and Access VBA help were integrated, which I liked. In Access 2000 and up, the Visual Basic Editor runs in a separate window (as was already the case for Word 97 and Excel 97), and the help files have been disentangled too...

Posting Permissions

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