Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parse last word in field (2002, SP3)

    Is there any way an expression can be created that will pull the last word from a field that can contain multiple words. I have tried various ways using Instr( ) but because there may be a different number of spaces in each record, I can not find a way to make this work. Also is there a way to count the number of spaces within a field?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Parse last word in field (2002, SP3)

    Is there a command called RevStr? Seem to remember something like that.

    For the spaces write a function.

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

    Re: Parse last word in field (2002, SP3)

    Try this expression in a query:
    <code>
    LastWord: Mid([FieldName],InStrRev([FieldName]," ")+1)
    </code>
    or in the control source of a text box on a form or report:
    <code>
    =Mid([FieldName],InStrRev([FieldName]," ")+1)
    </code>
    Replace FieldName with the name of your field.

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

    Re: Parse last word in field (2002, SP3)

    To count the number of spaces in a field in a query:
    <code>
    NumSpaces: Len([FieldName])-Len(Replace([FieldName]," ",""))
    </code>
    or in the control source of a text box on a form or report:
    <code>
    =Len([FieldName])-Len(Replace([FieldName]," ",""))
    </code>
    Again, substitute the correct field name.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse last word in field (2002, SP3)

    I can't seem to find any mention of Revstr in any of my Access manuals. Can you help me with creating a function to count the spaces?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse last word in field (2002, SP3)

    Thanks, that did the trick. It's funny that I was unable to find any mention of that expression in any of my books.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Parse last word in field (2002, SP3)

    Functions such as InStrRev and Replace were introduced in Office 2000, but they couldn't be used in expressions in queries, forms etc. in Access 2000. That only became possible in Access 2002.

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse last word in field (2002, SP3)

    Are replace and instrrev available in A2K? I get an "Undefined unction" error when I try to use them. I thought I had used Replace before, but maybe it was in Excel.

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

    Re: Parse last word in field (2002, SP3)

    As I noted in another reply in this thread, functions such as InStrRev and Replace cannot be used in expressions in Access 2000, although they can be used in VBA code. See ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions.
    Starting with Access 2002, these functions can also be used in expressions.

  10. #10
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse last word in field (2002, SP3)

    Thanks - I was mid-post when I got called away. Didn't see the "not available" one until after I posted.

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parse last word in field (2002, SP3)

    This is good code to save for future reference. Thanks a lot.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Parse last word in field (2002, SP3)

    Clever code Hans

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

    Re: Parse last word in field (2002, SP3)

    It's a well-known trick in Excel (using worksheet functions instead of VBA functions) to count the number of occurrences of a specific character in a cell.

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

    Re: Parse last word in field (2002, SP3)

    I'm starting to learn Excel, so i guess i am in for a few tricks in there.

Posting Permissions

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