How do you trim the last letter in access 2003?

Topic

New Reply

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.

Reply | Quote

Replies

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)))

Reply | Quote

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.

Reply | Quote