create a email address string in table

[hardrock]

hi all, I have a table full of names in format first name <space> surname. How can I create a string that joins these together with a Dot in the space and then add @company.com at the end? I aim to write this back to a table column called email address when done. Thanks

 

[georgedwilkinson]

update mytable
set emailaddress = replace(mynamecolumn,' ', '.') & '@company.com';

 

[hardrock]

great will try a little later. Thanks

 

[vbaInet]

You will need to do alot more than that actually. There could be cases where two or more employees have the same surname and firstname. In that case before updating you would check if that name already exists and if it does just add a 1 at the end (just before @) then perform georgedwilkinson's concat.

 

[georgedwilkinson]

Actually, if we're talking about things that can go wrong, they are numerous. My sample code will handle the basic requirements. You may need to go farther with "fixing" special characters (double dots?) or eliminating duplicates. It really depends on how clean your data is initially.

 

[vbaInet]

Absolutely. I tend to restrict the user to certain characters on data entry, that way you are 98% sure your data is clean.

 

[gemma-the-husky]

why do you want to assume that the email address is

firstname.secondname@company.com

it may not be that

isnt it better to store the email address, and do that by suggesting the email address based on the names, but enabling the user to modify it

ie - in the same way that outlook address book suggests email addresses

 

[hardrock]

@GeorgedWilkinson.

Have tried your code, but it updates my table column as per format below:

john.smith........................@company.c

The code is

Dim db As DAO.Database

Set db = CurrentDb

db.Execute "Update tbl_employees set Email = replace(Name,' ', '.') & '@company.com'"


Any ideas?

 

[vbaInet]

Why don't you trim both the first and surname and concat them?

Trim(Firstname) & "." & Trim(Surname) & "@company.com"

Error handlers should be there as well

 

[hardrock]

Can't use the trim as first and surname are both in same column separated by a space. The code kind of works, just unsure where all the ............ <dots> are coming from.

 

[vbaInet]

replace(Trim(Name),' ', '.',,1) & '@company.com

try that.

 

[hardrock]

Fanstastic! got rid of the comma before the 1, and it did the job. Many thanks

 

[vbaInet]

Fanstastic! got rid of the comma before the 1, and it did the job. Many thanks

Excellent job hardrock.