Blacklist Email Query

[jifitz]

Hello I am very new to Access. I have had 1 class which means that I can now break it. I have one column with a list of email addresses. I want to remove everything in front of the @ sign. Then I want to add a * in place of the user name. Also I need remove all duplicates with removing the original address (or Domain, by this point.)Once I have this list then I need to export it to .xml for GFI Mail Essentials. Any help would be greatly appreciated.

 

[rjoubert]

First, try this update query to get the * plus your domain (replace "TableName" with the name of your table, and "Email" with the name of the field storing your email addresses)...

UPDATE TableName SET TableName.Email = "*" & Mid([TableName]![Email],InStr(1,[TableName]![Email],"@",1),Len([TableName]![Email]));

Then, if you want to get a list of unique email addresses after that, you can do a Group By query...

SELECT TableName.Email
FROM TableName
GROUP BY TableName.Email;

 

[rjoubert]

If you are using Access 2003, you can export the results of the Group By query I gave you to XML. After you save the query, right click on it, and select the Export option. Then, in the Save as type dropdown, select the XML option.

 

[jifitz]

I really apperciate the code. I will give it a try. It will make my life easier and cut way down on spam. Beleive it or not I am using Access 2007 and I think it should be no problem to export to .xml.


Thanks Again.

 

[jifitz]

I got it to take the update query, but when I execute it I get "Enter Parameter Value". Here is what I edited it to work with my data:
[UPDATE GFIBlacklist SET GFIBlacklist].[BadEMail]="*" & Mid([GFIBlacklist]![BadEMail],InStr(1,[GFIBlacklist]![BadEMail],"@",1),Len([GFIBlacklist]![BadEMail]))
What am doing wrong?
Thanks for your patience with a newbie.

 

[rjoubert]

UPDATE GFIBlackList SET GFIBlacklist.BadEmail = ...

 

[PHV]

UPDATE GFIBlacklist
SET BadEMail='*' & Mid([BadEMail],InStr([BadEMail],'@'))
WHERE BadEMail Like '*@*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886