Text Box to show last name only

G

Gary Hull

I have a field in a table where I enter peoples names (Hull, Gary) the
field is bound to a text box on a form, what I need to do is be is to have
the text box on the field to show the Last name only.

Thanks for your help
 
A

Allen Browne

Use Instr() to locate the comma in the field.
Use Left() to pull out the previous characters.
Use IIf() to test if there is a surname.

The Control Source of the text box will be something like this:
=IIf(Instr([FullName],",")>1,Left([FullName],Instr([FullName],",")-1),False)
 
J

jahoobob via AccessMonster.com

You have discovered a "rule" of good database design. You should have
separate fields for the first name and last name (and middle name/initial).
You can then combine them in any way you need in queries e.g. [LastName] & ",
" & [FirstName] & " " & [MiddleInitial].
 
A

Allen Browne

Use:
- Len() to get the length of the field,
- Instr() to locate the comma,
- Mid() to read the rest of the field,
- Trim() to dump the leading space(s).

Note that you can omit the last argument with Mid(), and it picks up the
rest of the string from the n-th character.
 
Top