Strip All After Space?

[brucesilvers]

Problem: Need to compare one table's MemberName (single text field) that contains first and last name together (eg. Boris Badanoff) with another table's MemberName that contains only first name (eg. Boris). Names in 1st table are separated by a space. All first names in both tables are unique and length of first name varies.

Is there an Access function I could use in the Expression Builder to capture the first names only from the 1st table? If not, is there simple code I could call from a query to do so?

Thanks in advance for any help you can provide!

 

[WayneRyan]

bruce,

If you don't have to worry about there being no space:

Expr: Mid([FullName], 1, Instr(1, [FullName], " ") - 1)

Accounting for no space:

Expr: Iif(Instr(1, [FullName], " ") > 0,
          Mid([FullName], 1, Instr(1, [FullName], " ") - 1),
          [FullName])

hth,
Wayne

 

[brucesilvers]

Thanks so much!!

2nd expression worked perfectly.