By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,077 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Parsing out Firstname from Given Names

P: n/a
I am using an update query to parse out the Firstname of a person from a
field (GivenNames) in a list of names. The criteria I am using is

Left([GivenNames],InStr(1,[GivenNames]," ")-1)

This works fine if the person actually has more than one name, but gives me
errors if the person only has one name in the first place and puts a null
into the Firstname field in that instance. How can I get it to just copy
the single name into the Firstname field for cases where the person only has
one first name?

dixie
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Dixie,

For cases where the person only has one first name, there will be no space.
InStr(1,[GivenNames]," ") returns 0 when there is no space. Maybe you can work
that into what you are doing.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"dixie" <di****@dogmail.com> wrote in message
news:Lb****************@nnrp1.ozemail.com.au...
I am using an update query to parse out the Firstname of a person from a
field (GivenNames) in a list of names. The criteria I am using is

Left([GivenNames],InStr(1,[GivenNames]," ")-1)

This works fine if the person actually has more than one name, but gives me
errors if the person only has one name in the first place and puts a null
into the Firstname field in that instance. How can I get it to just copy
the single name into the Firstname field for cases where the person only has
one first name?

dixie

Nov 12 '05 #2

P: n/a

"dixie" <di****@dogmail.com> wrote in message
news:Lb****************@nnrp1.ozemail.com.au...
I am using an update query to parse out the Firstname of a person from a
field (GivenNames) in a list of names. The criteria I am using is

Left([GivenNames],InStr(1,[GivenNames]," ")-1)

This works fine if the person actually has more than one name, but gives me errors if the person only has one name in the first place and puts a null
into the Firstname field in that instance. How can I get it to just copy
the single name into the Firstname field for cases where the person only has one first name?

dixie


I think this will do what you describe:

Iif(InStr([GivenNames], " ") > 0, Left([GivenNames],InStr(1,[GivenNames],"
")-1), [GivenNames])

HTH,
Randy
Nov 12 '05 #3

P: n/a
Sure does, thanks Randy.
I have also learned that I can use an IIf statement in a query which I would
not have suspected previously.

dixie

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:My*******************@newssvr15.news.prodigy. com...

"dixie" <di****@dogmail.com> wrote in message
news:Lb****************@nnrp1.ozemail.com.au...
I am using an update query to parse out the Firstname of a person from a
field (GivenNames) in a list of names. The criteria I am using is

Left([GivenNames],InStr(1,[GivenNames]," ")-1)

This works fine if the person actually has more than one name, but gives me
errors if the person only has one name in the first place and puts a null into the Firstname field in that instance. How can I get it to just copy the single name into the Firstname field for cases where the person only

has
one first name?

dixie


I think this will do what you describe:

Iif(InStr([GivenNames], " ") > 0,

Left([GivenNames],InStr(1,[GivenNames]," ")-1), [GivenNames])

HTH,
Randy

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.