UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Splitting Full Name Into Two Fields, Access 2016    
 
   
alorenzini
post Jun 3 2020, 02:32 PM
Post#1



Posts: 834
Joined: 21-September 07
From: South Dakota


I have a field called TenantName and it contains the full name of the tenant in this format: Doe, Jane

I need to split the first name and the last name into separate fields called FirstName and LastName.

Any ideas?

Thank you,

Art Lorenzini
Sioux Falls, SD

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
theDBguy
post Jun 3 2020, 02:37 PM
Post#2


UA Moderator
Posts: 78,446
Joined: 19-June 07
From: SunnySandyEggo


Hi Art. You can create a custom function using Split(). Either that or just use a combination of Left(), Mid(), and InStr() functions.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Jun 3 2020, 03:36 PM
Post#3


UtterAccess VIP
Posts: 12,376
Joined: 10-February 04
From: South Charleston, WV


Split it on the comma. Split function.

--------------------
Robert Crouser
Go to the top of the page
 
John Vinson
post Jun 4 2020, 01:05 AM
Post#4


UtterAccess VIP
Posts: 4,298
Joined: 6-January 07
From: Parma, Idaho, US


If (and it's a big if, data entry errors are hard to avoid) EVERY SINGLE NAME has one, and only one, comma and it's in the right place, you can create a query with calculated fields

LastName: Left(TenantName, InStr([TenantName], "," -1)

and

FirstName: Trim(Mid([TenantName, InStr([TenantName], ",") + 1)

You can make this into an Update Query to update newly created fields, or an Append query to write new records into a new table; it depends on where you're going with this. It's probably best to have the name fields separate in any case so you're going in the right direction.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
alorenzini
post Jun 4 2020, 10:16 AM
Post#5



Posts: 834
Joined: 21-September 07
From: South Dakota


I am trying to run this in a update query:

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

But keep getting a Syntax error....

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
orange999
post Jun 4 2020, 10:52 AM
Post#6



Posts: 2,110
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


QUOTE
Left([TenantName], InStr([TenantName], "," -1)

But keep getting a Syntax error....


It seems you are missing a bracket

Try Left([TenantName], InStr([TenantName], ",") -1)

--------------------
Good luck with your project!
Go to the top of the page
 
alorenzini
post Jun 4 2020, 11:04 AM
Post#7



Posts: 834
Joined: 21-September 07
From: South Dakota


THe first name worked great! But when I run the last name I received a Invalid bracketing of name '[TenantName, Instr([TenantName]'.

Trim(Mid([TenantName, InStr([TenantName],",")+1)

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
orange999
post Jun 4 2020, 11:14 AM
Post#8



Posts: 2,110
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


QUOTE
Trim(Mid([TenantName, InStr([TenantName],",")+1)


Try
Trim(Mid([TenantName, InStr([TenantName],",")+1))


3 left brackets, but only 2 right brackets...

--------------------
Good luck with your project!
Go to the top of the page
 
alorenzini
post Jun 5 2020, 11:03 AM
Post#9



Posts: 834
Joined: 21-September 07
From: South Dakota


I think I tried everything with this...

Trim(Mid([TenantName, InStr([TenantName],",")+1)

Invalid bracketing of name....

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
RJD
post Jun 5 2020, 11:05 AM
Post#10


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hi Art: PMFJI, but you are missing a right bracket ... and a closing paren ...

Trim(Mid([TenantName], InStr([TenantName],",")+1))

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
alorenzini
post Jun 5 2020, 11:29 AM
Post#11



Posts: 834
Joined: 21-September 07
From: South Dakota


That worked Great! Thank you everybody for the help!!!!

--------------------
Thanks

Art Lorenzini
Sioux Falls, SD
Go to the top of the page
 
RJD
post Jun 5 2020, 11:30 AM
Post#12


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th July 2020 - 05:26 AM