Selasa, 31 Desember 2013

Re: [MS_AccessPros] Replacing e-mail addresses with names

 

Thank you very much.  The second option worked great.  I had to write a line for each e-mail address but that will make it much easier in the future when we get new employees.  You help is greatly appreciated.
 
Happy New Year,
 
Dan

From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, December 30, 2013 4:09 PM
Subject: Re: [MS_AccessPros] Replacing e-mail addresses with names
 
Dan-

Try this:

DoCmd.RunSQL "Update tblOne SET [User List] ='Daniel Jones' WHERE InStr([User List], mailto:'danieljones@email.com') <> 0"

However, if there are multiple email addresses, you'll wipe out the others.

Consider also:

DoCmd.RunSQL "UPDATE tblOne SET [User List] = Replace([User List], 'danieljones@email.com', 'Daniel Jones')

That will simply replace the email with the name, but leave any other email in the field intact.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)

On Dec 30, 2013, at 9:31 PM, Daniel Daniel <dancg86@yahoo.com> wrote:
John,
 
Here is the Update SQL that works for single entries:
 
DoCmd.RunSQL "Update tblOne SET [User List] ='Daniel Jones WHERE [User List] = 'danieljones@email.com'"
 
If the field is: danieljones@email.com I get Daniel Jones
If the field is: danieljones@email.com, mikesmith@email.com Nothing is changed.
I would like the second example to be: Daniel Jones, Mike Smith
 
Thank you,
 
Dan
 
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, December 30, 2013 3:01 PM
Subject: Re: [MS_AccessPros] Replacing e-mail addresses with names
 
Dan-

It would help to see the SQL of your UPDATE query and perhaps some example contents of the fields you're trying to change and what you expect as a result.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)

On Dec 30, 2013, at 7:40 PM, dancg86@yahoo.com wrote:
I have a field in a table that contains e-mail addresses.  Sometimes there is one e-mail address sometimes multiple.  I created SQL to change the e-mail to the persons name.  It works great as long as there is only one e-mail address in the field.  I have searched the Microsoft websites as well as several others and they have been no help. I tried using "Application.SetOption "Default Find/Replace Behavior",1" and it does not do anything to help me.  I am currently using a update query with separate table to complete this task, but as more people join the company the table is getting very bulky. Is it possible for the REPLACE function to search "any part of field" in SQL? Also if anyone can suggest another way to accomplish this I am open to new way of accomplishing it.
 
I am  using Access 2007 with Windows 7 professional.
 
Thank you,
 
Dan

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar