Solved Extracting first name if more than one name. (1 Viewer)

ypma

Registered User.
Local time
Today, 03:04
Joined
Apr 13, 2012
Messages
643
The database I am working on has a field named Firstname and within that field one or two names can be inputted, I wish to extract only the first of these names. I entered the following into the field of a the query.

KnownAs: Left([FirstName],InStr([FirstName]," ")-1)

The result as shown below is ok when there are two names in the field , however when there is only one name I receive and error #Func!

ClientNumber...... KnownAs........FirstName

100058...................John .................. John Charkas

100059..................#Func!............... Richard

I am missing something and would like assistance
Regards Ypma
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:04
Joined
Oct 29, 2018
Messages
18,742
Try adding an IIf(). For example:
Code:
KnownAs: IIf(InStr([FirstName]," ")=0,[FirstName],Left([FirstName],InStr([FirstName]," ")-1))
 

ypma

Registered User.
Local time
Today, 03:04
Joined
Apr 13, 2012
Messages
643
theDbGuy, Once again a question has been asked and you have provided the perfect solution. Thank you.

Regards Ypma
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:04
Joined
Oct 29, 2018
Messages
18,742
Hi. You're welcome. Glad we could assist. Good luck with your project.
 

ypma

Registered User.
Local time
Today, 03:04
Joined
Apr 13, 2012
Messages
643
Hi. You're welcome. Glad we could assist. Good luck with your project.
theDBguy: You answered my question , however I did not consider the possibility of only one name or even none in the first name field . Do I have to add an is null in the expression you provided or a double IFF .

Your further input would be appreciated

Regards Ypms
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:04
Joined
Oct 29, 2018
Messages
18,742
theDBguy: You answered my question , however I did not consider the possibility of only one name or even none in the first name field . Do I have to add an is null in the expression you provided or a double IFF .

Your further input would be appreciated

Regards Ypms
The code I gave you should handle records with only one name. For missing names, you could try using the Nz() function. For example:
Code:
KnownAs: IIf(InStr(Nz([FirstName],"")," ")=0,...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:04
Joined
May 7, 2009
Messages
16,076
without using IIF() Or Nz() function, you can use (will handle Null also).

KnownAs: Left$([FirstName] & " ",InStr(1,[FirstName] & " "," ")-1)
 
Last edited:

ypma

Registered User.
Local time
Today, 03:04
Joined
Apr 13, 2012
Messages
643
without using IIF() Or Nz() function, you can use (will handle Null also).

KnownAs: Left$([FirstName] & " ",InStr(1,[FirstName] & " "," ")-1)
arnelgp: Your solution produce the right result, however when I changed to the following
KnownAs2: Left$([FirstName2] & " ",InStr(1,[FirstName2] & " "," ")-1) the result is blank
The only change is the addition of the numeral 2.

The first name is required for a salutation eg. Dear John.

Any ideas why adding 2 to your expression should not work, i have checked that there is data within Firstname2

Appreciate any thoughts

Ypma
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:04
Joined
Jan 23, 2006
Messages
14,240
Do you have a field in your table named FirstName2?
 

ypma

Registered User.
Local time
Today, 03:04
Joined
Apr 13, 2012
Messages
643
Do you have a field in your table named FirstName2?
Thank you for your interest, yes I have a field name FirstName and for the second client FirstName2 . My end game is to to be able to joint the two and have salvation Dear John and Kate. If there is not a second client then Just dear John..
Regards Ypma
 

Isaac

Lifelong Learner
Local time
Yesterday, 19:04
Joined
Mar 14, 2017
Messages
6,270
Thank you for your interest, yes I have a field name FirstName and for the second client FirstName2 . My end game is to to be able to joint the two and have salvation Dear John and Kate. If there is not a second client then Just dear John..
Regards Ypma
Why not just use the solution given in post 6?

When choosing between 2 otherwise-equal expressions, my advice is, choose the one that's easier to eyeball-read quickly and quickly understand.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:04
Joined
Jan 23, 2006
Messages
14,240
Can you tell us more about how and where Kate would be in your table? I'm not sure of your design. Perhaps you could show us your table design.
 

ypma

Registered User.
Local time
Today, 03:04
Joined
Apr 13, 2012
Messages
643
Why not just use the solution given in post 6?

When choosing between 2 otherwise-equal expressions, my advice is, choose the one that's easier to eyeball-read quickly and quickly understand.
I could not make the solution in post 6 work as i did not know how to complete the expression. and tried other ways to achieve my goal. As stated I require both fields to produce the same result

Regards Ypma
Can you tell us more about how and where Kate would be in your table? I'm not sure of your design. Perhaps you could show us your table design.
Within the client table the first applicant has a field , FirstName and the partner to the first applicant has a field Firstname2, in my scenario Kate is the second named applicant . On occasions there is no second applicant hence the need for a blank result for that field ..

Regards Ypms
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:04
Joined
Oct 29, 2018
Messages
18,742
I could not make the solution in post 6 work as i did not know how to complete the expression. and tried other ways to achieve my goal.
It would be nice if you can let us know when you are having problems with someone's suggestion, so we can elaborate on it for you.
 

ypma

Registered User.
Local time
Today, 03:04
Joined
Apr 13, 2012
Messages
643
It would be nice if you can let us know when you are having problems with someone's suggestion, so we can elaborate on it for you.
You are right , I should have admitted that I could not complete your suggestion in post 6. Can I push my luck and request you complete .
KnownAs: IIf(InStr(Nz([FirstName],"")," ")=0,...

Regards Ypma
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:04
Joined
Oct 29, 2018
Messages
18,742
You are right , I should have admitted that I could not complete your suggestion in post 6. Can I push my luck and request you complete .
KnownAs: IIf(InStr(Nz([FirstName],"")," ")=0,...

Regards Ypma
No promises...

This is what I gave you earlier, which you said works:
Code:
KnownAs: IIf(InStr([FirstName]," ")=0,[FirstName],Left([FirstName],InStr([FirstName]," ")-1))

Here's what I suggested you tried next. Please let us know if you don't see the difference:
Code:
KnownAs: IIf(InStr(Nz([FirstName],"")," ")=0,[FirstName],Left([FirstName],InStr([FirstName]," ")-1))
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:04
Joined
May 7, 2009
Messages
16,076
see query1 for this demo.
 

Attachments

  • concatNames.accdb
    404 KB · Views: 373

Users who are viewing this thread

Top Bottom