Include only first part of a field in a query

[Guest]

Hello,

I have a field called Given Names and it includes both the first and the
middle name of a customer. I would like to break the two names into
individual fields. In other words I want to show just the first name in a
field and just the middle name in a second field. How do I do this in a
query? Thanks.

Janet

 

[David S via AccessMonster.com]

Assuming that the first and middle names are separated byt a single space,
you can do this by locating that space in the string and then taking the
portions to either side of it, like so:
SELECT NameTable.Name, Left([name],InStr([name]," ")-1) AS Part1, Right([name]
,Len([name])-InStr([name]," ")) AS Part2
FROM NameTable;

Note that this doesn't work if the names are separated by something other
than a space, and if they're separated by more than one space you'll get
spaces in front of Part2. It also doesn't handle multiple spaces in the name
very well, but maybe it will do for your purposes...

 

[David S via AccessMonster.com]

Actually, I've just remembered that you can get rid of the extra spaces using
TRIM:
SELECT NameTable.Name, Left([name],InStr([name]," ")-1) AS Part1, Trim(Right(
[name],Len([name])-InStr([name]," "))) AS Part2
FROM NameTable;