How to "un-concatenate" a field in table? 2

[Louise99]

I have a field with a person's name listed as "Smith,Mary" - I guess whoever created the table didn't think to separate the last name - yikes.

I know how to separate this type of field in Excel(using the Text to Column). What should I do to separate it using an expression in a query?

Any help appreciated!!!



Thanks!

 

[PHV]

Have a look at the Split function.
Another way is to use the InStr, Left and Mid functions.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

There have been lots of threads regarding this issue. You can use Instr() to find the location of the "," within the full name and Left(), Right(), and Mid() to return various chunks of the full name.

If you can't figure this out, open any module window and search Help.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Louise99]

dhookom,

The help function on our network version of Access isn't working or I would have looked up the left, right and mid.

One question for you - how would get the left function to return a name if all the names are different number of characters?

Thanks!

 

[PHV]

The help function on our network version of Access isn't working
Your main issue is above.
Please correct this problem before asking in this forum.

 

[Louise99]

I'm sorry but I'm not understanding something. Is there a problem with my posting a simple question?

PHV, obviously I'm not an I.T. guru in charge of fixing the Access help or I wouldn't have posted at all. dhookom,you state "There have been lots of threads regarding this issue" but I used the search function in tek-tips using many combination of terms looking for a similar problem without success. Then, when I finally post my problem, I get what seems to be condescension and sarcasm in each answer.

Perhaps it's just an innocent error that the posts I received seemed quite unfriendly, pedantic and snotty. It could be similar to when some people don't realize sending an email in all caps may cause the recipients to think they are SHOUTING AT THEM.

Would your answers be different if my handle wasn't a female name?

It's not like I asked where the "any" key is . . .

I sincerely thank you all for your help.

 

[MoLaker]

Easy Louise. The responses you got were directed towards helping you help yourself. Your question is not quite as simple as just giving you an expression to apply. What you want to do requires some basic knowledge of VBA and/or string manipulation. dhookom's response is spot on. PHV's response is telling you what you need to get going. If you do not have the ability/authority to correct the Help issue, your IT department surely does. Without meaning to be sarcastic or denigrating in any way, these are good suggestions. As for the "female" question, try another forum. We try to be gender free here.

"Retired Programmer", that is. So, please be patient.

 

[dhookom]

I hope my reply didn't offend you. It would have been the same regardless of your gender. If your original post had suggested something like "I have searched but couldn't find and my Help doesn't function..." I would have provided the basic syntax of Mid(), Instr(), and Left().

Your original posting left me with the impression you have some experience with Office and creating expressions. I prefer to teach you to fish rather than giving you a fish.

If you could open Help, you would find that Instr()

Returns a Variant (Long) specifying the position of the first occurrence of one string within another.
Syntax
InStr([start, ]string1, string2[, compare])

Finding the position of the comma would be:
Instr([FullName],",")
The Left() function

Returns a Variant (String) containing a specified number of characters from the left side of a string.
Syntax
Left(string, length)

And Mid()

Returns a Variant (String) containing a specified number of characters from a string.
Syntax
Mid(string, start[, length])

Or, since we both provided the exact function names, you could have found all this by googling "Access Left()"

http://www.google.com/search?hl=en&q=Access+Left()

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[MoLaker]

Perhaps these will get you started.

http://www.mvps.org/access/strings/str0001.htm

http://www.mvps.org/access/strings/str0003.htm

"Retired Programmer". So, please be patient.

 

[Louise99]

Thanks very much MoLaker and dhookom. Your latter posts are VERY helpful (yeah, I shouted VERY).

I'm sorry I shorted out a bit. I've been dealing with problems such as this:

1. Internet is down.
2. Called the help desk.
3. Put on hold by the help desk for eight minutes with scratchy musak.
4. Finally left message with problem, asking for call back.
5. Get voice mail from then telling me to "go on our website" for further assistance.

So, the internet is down and I need to GO ON THE INTERNET FOR HELP?

Also, someone stole my Access "bible."

Okay. That's my sob story. I feel better now.

Thanks so much for your help. Hope we all have a great weekend.

 

[dhookom]

Louise99,
Glad to be of some help. I will have a greate weekend (regardless of the -0 F temps) if the Packers can win on Sunday ;-)

BTW: put a chain on your Access "bible".

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[MajP]

Sometimes ya just want the fish.
This assumes that all names are really entered
FirstName, LastName
This would be more involved for middle names and initials.

in a standard module:

Public Function getFirstName(stringName As String) As String
  getFirstName = Left(stringName, commaPosition(stringName) - 1)
End Function

Public Function getLastName(stringName As String) As String
 getLastName = Trim(Mid(stringName, commaPosition(stringName) + 1))
End Function

Public Function commaPosition(stringName As String) As Integer
  commaPosition = InStr(1, stringName, ",")
End Function

now in a query you can make two calculated fields. Lets say the old field name is something like "strName". Then in the query builder do:
strFirstName:getFirstName([strName])
and make another calculated field
strLastName:getLastName([strName])

Now see if you return the first and last names. Now take this query and turn it into a make table query. You should get a new table with two new fields "strFirstName" and "strLastName