Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2013
    Posts
    14

    Unanswered: Sorting words within a field

    Is there a way to sort words within a field alphabetically.

    E.G.

    field 1
    Paul Mary John

    would be changed to

    John Mary Paul

    I don't really know how to use code etc. so if its possible baby steps would be great.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes its doable
    probably need to write a function whch accepts the names as a parameter, and returns the sorted value

    you'd need to use the split function which returns an array or words,
    then do a sort of that array and return the value

    I'd be concerned on performance if you used this function intensively

    Code:
    public function sortwords(source as string, optional separator as string = " ") as string
        sortwords = source
    'validations
    'well check there is something in Source
    if isnull(source) or len(source)<1 then exit function
    ' check that there is something in separator
    if isnull(separator) p len(separator)<1 then exit function
    ' AND check there is a least one separator in the source
    if instr(source,separator) <=0 then exit function
    'ok so we know that we have valid data
    
    dim words() as string
    dim thisword as string
    words = split(source, separator)
    Dim iLoop as integer
    Dim oLoop as integer
    'perform a bubble sort, moving the lower value up the list
    for iLoop = 0 to ubound(words)
      for oLoop = ubound(words) to iLoop step -1
         if words(oloop) words(iLoop) then
          thisword = words(iLoop)
          word(iLoop) = word (oLoop)
          word(oLoop) = thisword
         endif
      next oLoop
    next iLoop
    
    sortwords = ""  'reset the return variable
    'build our sorted return variable
    for iLoop = 0 to ubound(words)
      sortwords = sortwords & " " & words(iloop)
    next iLoop
    sortwords = trim(sortwords) 'remove any trailing spaces
    end function
    then call the function from wherever

    eg
    select sortwords(forenames) as Sotednames, forenames from mytable
    or in a control on a report or form =sortword(mywordycolumn)
    or in an update query:- update mytable set forenames = sortwords(forenames)

    as ever the above code is untested, will contain typos. may contain logic errors... its typed as is
    you will nbeed to thoroughly test and debug it to make certain its suitable for your needs
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2013
    Posts
    14
    Hi, thanks for the reply.

    As I mentioned I'm not really sure how to use code but I compiled it and corrected the errors it flagged (I think).

    When I put it into a query it says sortwords is an undefined expression?

    What am I doing wrong?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Put the code into a code module not a form or report
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2013
    Posts
    14
    Yup, I created a new module. Pasted in your code. Clicked debug > compile then wrote what you suggested in an update table query.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is the function declared as public
    have you check there are no typos
    have you checked the code compiles

    have you considered calling the function from within a form to prove it works, before stuffing it into a query. if needs be place a breakpoint on the function and step trhoigh the code to prove it works, or show you where it doesn't
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2013
    Posts
    14
    I've managed to make it find the right module now however it doesn't like the following 2 lines and since I am a code noob I'm not sure what they are meant to be. It wants a "then" where the p is and after the oloop.

    if isnull(separator) p len(separator)<1 then exit function

    if words(oloop) words(iLoop) then

    Sorry to be a pest. Its the last bit I need to make things work >_<

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    oops
    Code:
    if isnull(separator) p len(separator)<1 then exit function
    should read
    Code:
    if isnull(separator) >= len(separator)<1 then exit function
    Code:
    if words(oloop) words(iLoop) then
    should read
    Code:
    if words(oloop) words(iLoop) then
    there is probably some code missing off the sort routine. its a while since I looked at a bubble sort.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2013
    Posts
    14
    Hi, thanks for all your help.

    Sorry I don't see the differences in the second bit of code?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    good point well made
    if words(oloop) <=words(iLoop) then

    im pretty certain there is some missing code. you might want to look at Access implementations of a bubble sort
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2013
    Posts
    14
    Yeah it runs but doesn't do anything at the moment. I will have a look at what you suggested. Thanks again. Really appreciate it.

  12. #12
    Join Date
    Aug 2013
    Posts
    14
    I managed to get it to work by deleting your checks for valid data!

    Is that going to cause any issues? (this is probably a stupid question but it seemd to not cause any errors with my test data)

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    put a break point ont he code and step through it
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Aug 2013
    Posts
    14
    Ummm I have no idea what that means sorry.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try calling it with sortwords(mywordcolumn, " ")

    the optional separator parameter is intedned so this code can be used with other separators, not just a space
    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
  •