split full name(last, first) to last name and first name fields

G

Guest

I'm an Access novice. I've found answers like UPDATE table SET
lastname=Left([fullname]],InStr(fullname],",")-1 I vreated a Query, changed
it to Update Query and put that code in "Update to" field of lastname but all
I get is a 0(zero) in last name field. Should code go in "Criteria" field ?
I'm missing something.
 
K

Ken Snell \(MVP\)

Show us sample data for your fullname values.

The expression goes in the "Update To:" field.

I do note a syntax error, though, in the expression that you posted. Change
it to this:

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

--

Ken Snell
<MS ACCESS MVP>

"Access Novice in Confusion" <Access Novice in
(e-mail address removed)> wrote in message
news:[email protected]...
 
E

Eric D via AccessMonster.com

Add this to your "Update to" field in your query.

Left([FullName],InStr(1,[FullName],",")-1)
Should give you the LastName - when FullName = LastName, FirstName

Mid([FullName],InStr(1,[FullName],",")+1)
Should give you the FirstName - when FullName = LastName, FirstName

Review your documentation on the InStr function.
Based on your note, I'm surprised you got the query to run at all. The syntax
in your example is incorrrect. I mention this as syntax is just about the
MOST important thing one should look at when debugging.

Hope this helps.
I'm an Access novice. I've found answers like UPDATE table SET
lastname=Left([fullname]],InStr(fullname],",")-1 I vreated a Query, changed
it to Update Query and put that code in "Update to" field of lastname but all
I get is a 0(zero) in last name field. Should code go in "Criteria" field ?
I'm missing something.
 
J

John Spencer

Two things.
One you have a syntax error in what you posted.
Two you may be putting too much in the update to field.

Field: LastName
Update: Left([TableName].[FullName],Instr(1,[TableName].[Fullname],",")-1)
 

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