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

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

http://www.viescas.com/

(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

http://www.viescas.com/

(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