Separate By Comma

B

Beagle1927

Hello,

I have one field that has a comma between the first and last name. The
rest of the fields are fine. Is tehre any way to separate the first
and last name?

If it is easier in Excel..Please let me know.
 
G

Guest

x = "Clark, Steve"
intComma = instr(x, ",")
strLastName = Left$(x, intComma-1)
strFirstName = Mid$(x, intComma+2)
 
G

Guest

Since this is in the Queries room, I would guess that is what you are trying
to do.
If you are using the Query builder, you can create two calculated fields. I
am assuming there will be a space following the comma as in "Longbow, Frodo"

Fname: Left([Name],Instr([Name],",")-1)
Lname: Mid([Name],Instr([Name],"<")+2)
 
B

Beagle1927

Since this is in the Queries room, I would guess that is what you are trying
to do.
If you are using the Query builder, you can create two calculated fields. I
am assuming there will be a space following the comma as in "Longbow, Frodo"

Fname: Left([Name],Instr([Name],",")-1)
Lname: Mid([Name],Instr([Name],"<")+2)
--
Dave Hargis, Microsoft Access MVP



Beagle1927 said:
I have one field that has a comma between the first and last name. The
rest of the fields are fine. Is tehre any way to separate the first
and last name?
If it is easier in Excel..Please let me know.- Hide quoted text -

- Show quoted text -

Thanks all..worked like a charm. If it isn't too much trouble could
you walk me through what the expressions are doing?
 
M

Michel Walsh

A function does not 'starts' before all its arguments are fully evaluated.
So, in

Left([Name],Instr([Name],",")-1)


Left() has to evaluate each of its argument.
[Name] is the string (coming from the field value) to be parsed, ok, it
will be "Longbow, Frodo".
InStr( ) -1 constains a function to be evaluated, InStr. So, let us do
it


InStr( [Name], "," )

The help file tell us that InStr(a, b) returns the position of the
first occurrence of string hold in its second argument, b, within its first
string argument, a. If no occurence is found, it returns 0.

InStr("Longbow, Frodo", ",")

should then return the first occurrence of a coma in: "Longbow, Frodo"
Which is 8, because in position 8, there is the first coma.

So, InStr() returns 8, and InStr()-1 returns thus 7.

Left("Longbow, Frodo", 7)

from the help file, it tell us that the first 7 characters of the string
would be returned, that is, "Longbow" (without hte coma).


The second expression:

Mid( [Name], Instr( [Name], "," )+2 )


is similar. InStr returns, again, 8, so the second argument of Mid() is 10.


Mid("Longbow, Frodo", 10)

would return: "Frodo"



As for the LName: and FName: those indicate name for the columns made of
computed expression.


Hoping it may help,
Vanderghast, Access MVP


Beagle1927 said:
Since this is in the Queries room, I would guess that is what you are
trying
to do.
If you are using the Query builder, you can create two calculated fields.
I
am assuming there will be a space following the comma as in "Longbow,
Frodo"

Fname: Left([Name],Instr([Name],",")-1)
Lname: Mid([Name],Instr([Name],"<")+2)
--
Dave Hargis, Microsoft Access MVP



Beagle1927 said:
I have one field that has a comma between the first and last name. The
rest of the fields are fine. Is tehre any way to separate the first
and last name?
If it is easier in Excel..Please let me know.- Hide quoted text -

- Show quoted text -

Thanks all..worked like a charm. If it isn't too much trouble could
you walk me through what the expressions are doing?
 
G

Guest

Actually, there is an error here. It will work, but end up with the wrong
names, so change the names:
Lname: Left([Name],Instr([Name],",")-1)
Fname: Mid([Name],Instr([Name],"<")+2)

The first expression is creating a field in the query named Lname. It will
contain the last name of the person. it looks at the Name field in the table
that contains the LastName, a comma, a space, and the First Name. The Left
function returns the left portion of the text string for the length
specified. For this we use the Instr function which returns the position of
one string within the other. We look in the NAME field for the comma and
subtract 1 which will tell us how many characters to return starting from the
left. The first name works the same way except we use the Mid function so we
know where to start the return of characters.
--
Dave Hargis, Microsoft Access MVP


Beagle1927 said:
Since this is in the Queries room, I would guess that is what you are trying
to do.
If you are using the Query builder, you can create two calculated fields. I
am assuming there will be a space following the comma as in "Longbow, Frodo"

Fname: Left([Name],Instr([Name],",")-1)
Lname: Mid([Name],Instr([Name],"<")+2)
--
Dave Hargis, Microsoft Access MVP



Beagle1927 said:
I have one field that has a comma between the first and last name. The
rest of the fields are fine. Is tehre any way to separate the first
and last name?
If it is easier in Excel..Please let me know.- Hide quoted text -

- Show quoted text -

Thanks all..worked like a charm. If it isn't too much trouble could
you walk me through what the expressions are doing?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top