Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    20

    Unanswered: How to extract text from a textfield

    I was hoping if someone could help me with extracting texts from a textfield in a form. For example if the text or string is: "orange, apple, strawberry"

    I would like to be to arrange them in another textfield (textfield2) as: "Apple Orange Strawberry" without the comma.

    I tried using the split function but if the textfield only had 1 value, it gives me an 'out of range' error.

    I would really appreciate any help. Thank you.

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Completely misread your question...too much cold medicine. Ignore me...


    C

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You'd need to write your own function to do that.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can use the Replace() function, if they can stay in the same order.
    Paul

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As Paul said

    Replace(YourString,",","")

    If you want the words capitalized, as your example shows

    StrConv(Replace(YourString,",",""), vbProperCase)
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2006
    Posts
    20
    Thank you so much...I'll try that.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    replace will remove the comma, but it wont place the words in ascending order, not will it capitalise the words. As Startrekker says you will need to write your own function to handle this. That function will have to find the number of words, sort them, change the capitalisation and rebuild the array

    Id suggest the underlying issue is that you violated the first normal form, ie your db design is flawed

    however this should get you in the rigth direction
    Code:
    Private Function SortWords(aString As variant, Optional aSeparator As String = ",") As String
    'this function requires a string containing a list of words, and optionally a separator
    'it sorts the words into ascending order and then returns that sorted list
    'whilst we are at it the words are converted to proper case ie FRUIT becomes Fruit, Veg remains Veg, object becomes Object...
      Dim myWords() As String will contain the individual words SPLIT from the source aString
      Dim noWords As Integer 'the number of words found 
      Dim iLoop As Integer 'loop counter
      Dim oLoop As Integer 'loop counter
      Dim tempWord As String 'conatins a temporary word that is int he process of being swapped
      SortWords = "" 'set up our default return
      'make sure we have some valid data, otherwise return an empty string
      If IsNull(aString) Or Len(aString) = 0 Then Exit Function
      myWords = Split(aString, aSeparator) 'split the source string into individual words using the supplied separator
      'ok lets find the number of words
      noWords = 0
      For Each thsWord In myWords
        'whilst we are at it convert the individual words to capitalised (proper case)
        myWords(noWords) = StrConv(myWords(noWords), vbProperCase)
        noWords = noWords + 1
      Next
      'as split generates a zero based array...
      noWords = noWords - 1
      'this is a simple bubble sort.. is good enough for the expected number of words
      For oLoop = 0 To noWords - 1
        For iLoop = oLoop To noWords
          'test if the outer loop word is greater than the inner loop, if so exchange the words
          If myWords(oLoop) > myWords(iLoop) Then 
            tempWord = myWords(oLoop)
            myWords(oLoop) = myWords(iLoop)
            myWords(iLoop) = tempWord
          End If
        Next iLoop
      Next oLoop
      SortWords = Join(myWords) 'rebuild the list from the array
      'jobsagoodun, bail out of the function
    End Function
    Last edited by healdem; 04-29-09 at 05:17.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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