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?