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.

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" wrote:

> 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.
> 
> 

On Sep 19, 1:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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.- 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?

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" <ebonystealth@hotmail.com> wrote in message 
news:1190227827.316218.220350@50g2000hsm.googlegroups.com...
> On Sep 19, 1:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
>> 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" wrote:
>> > 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.- 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?
> 

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" wrote:

> On Sep 19, 1:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > 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.- 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?
> 
>