Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do you trim the last letter in access 2003?

    If more then 1 letter is at the end of a persons name after the last space do nothing but if only 1 letter is after the name then take that letter off in access 2003?
    Smith,John A--I would want to get rid of the A
    but if it was Smith,John Ann-I don't want it to do anything.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Try the following but substitute Name for the name of your control:

    Mid(Name, 1, IIf(Mid(Name, Len(Name)-1, 1) = " ") , Instr(1, Name, Len(Name)-2), Instr(1, Name, Len(Name)))
    Last edited by patt; 2011-09-27 at 17:50.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by buckshot View Post
    If more then 1 letter is at the end of a persons name after the last space do nothing but if only 1 letter is after the name then take that letter off in access 2003?
    Smith,John A--I would want to get rid of the A
    but if it was Smith,John Ann-I don't want it to do anything.
    What does "do nothing" mean? Just display the whole name as it is?

    Patt has used the Instr function. You can also use the InstrRev function which looks for things from the end rather than the beginning.

    InstrRev([txtName]," ") tells you the position of the last space, counted from the begining.
    InstrRev("j smith a"," ") =8


    So InstrRev([txtName]," ") = len([txtname])-1 is a test for whether the last space is one place from the end.
    Regards
    John



Posting Permissions

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