PC Review


Reply
Thread Tools Rate Thread

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

 
 
=?Utf-8?B?QWNjZXNzIE5vdmljZSBpbiBDb25mdXNpb24=?=
Guest
Posts: n/a
 
      2nd Jan 2006
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.
 
Reply With Quote
 
 
 
 
Ken Snell \(MVP\)
Guest
Posts: n/a
 
      2nd Jan 2006
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
http://www.pcreview.co.uk/forums/(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



 
Reply With Quote
 
 
 
 
Eric D via AccessMonster.com
Guest
Posts: n/a
 
      2nd Jan 2006
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.

Access Novice in Confusion wrote:
>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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200601/1
 
Reply With Quote
 
John Spencer
Guest
Posts: n/a
 
      2nd Jan 2006
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)

Access Novice in Confusion wrote:
>
> 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: creating last name first, first name last from a column of first name last name data Patrick Molloy Microsoft Excel Programming 0 27th Jul 2009 08:17 AM
Re: creating last name first, first name last from a column of first name last name data Lars-Åke Aspelin Microsoft Excel Programming 0 27th Jul 2009 07:32 AM
Re: creating last name first, first name last from a column of first name last name data Rick Rothstein Microsoft Excel Programming 0 27th Jul 2009 07:25 AM
Re: last name first, first name last -- is there a code to combine 2 cells with last and first names? Patrick Molloy Microsoft Excel Programming 1 17th Jul 2009 12:40 PM
how do I reveerse name order last name, first name to first name, last name Don Smith Microsoft Excel Discussion 9 29th Nov 2006 10:55 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:28 AM.