Update Query Question!

[JOM]

I have a bout 600 records of employee names in my tblEmployee. I have there
first name as e.g., John A. and EmplLastName as e.g., Smith. I have
EmplEmail that is supposed to be e.g., [email protected] Wherer
Company Name represents the company's name.

I would like to create an update query that will join the First Name and
Last Name, add the period between John and A and if there is only one name
provided as John then it should be John.Smith therefore add the period after
John...

E.g., [email protected] ( if this is what was provided as name
John A. Smith)
[email protected] (If this is what was provided as Name
Jane Shell)

I hope I have explained in detail, anyone please help!

 

[Allen Browne]

In your Update query, try an expression like this:

Replace([First Name] & "." & [Last Name], " ", ".") & "CompanyName.com"

 

[JOM]

Thanks for the response, however there is a problem.......

I have a name John Smith when I do the replace, it works ok but the problem
is for the names that have John A. as first name and Smith as last name it
becomes
john.A..Smith ie it adds the extra period before the last name...
it should be john.A.Smith

In your Update query, try an expression like this:

Replace([First Name] & "." & [Last Name], " ", ".") & "CompanyName.com"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a bout 600 records of employee names in my tblEmployee. I have
there
first name as e.g., John A. and EmplLastName as e.g., Smith. I have
EmplEmail that is supposed to be e.g., [email protected]
Wherer
Company Name represents the company's name.

I would like to create an update query that will join the First Name and
Last Name, add the period between John and A and if there is only one name
provided as John then it should be John.Smith therefore add the period
after
John...

E.g., [email protected] ( if this is what was provided as name
John A. Smith)
[email protected] (If this is what was provided as Name
Jane Shell)

I hope I have explained in detail, anyone please help!

 

[Allen Browne]

So you want to strip out the existing dots from the First Name?

Replace(Replace([First Name], ".", "") & "." & [LastName], " ", ".") &
"Company.com"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the response, however there is a problem.......

I have a name John Smith when I do the replace, it works ok but the
problem
is for the names that have John A. as first name and Smith as last name it
becomes
john.A..Smith ie it adds the extra period before the last name...
it should be john.A.Smith

In your Update query, try an expression like this:

Replace([First Name] & "." & [Last Name], " ", ".") &
"CompanyName.com"

I have a bout 600 records of employee names in my tblEmployee. I have
there
first name as e.g., John A. and EmplLastName as e.g., Smith. I have
EmplEmail that is supposed to be e.g., [email protected]
Wherer
Company Name represents the company's name.

I would like to create an update query that will join the First Name
and
Last Name, add the period between John and A and if there is only one
name
provided as John then it should be John.Smith therefore add the period
after
John...

E.g., [email protected] ( if this is what was provided as
name
John A. Smith)
[email protected] (If this is what was provided as Name
Jane Shell)

I hope I have explained in detail, anyone please help!

 

[JOM]

Thanks, that worked perfectly well

So you want to strip out the existing dots from the First Name?

Replace(Replace([First Name], ".", "") & "." & [LastName], " ", ".") &
"Company.com"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the response, however there is a problem.......

I have a name John Smith when I do the replace, it works ok but the
problem
is for the names that have John A. as first name and Smith as last name it
becomes
john.A..Smith ie it adds the extra period before the last name...
it should be john.A.Smith

In your Update query, try an expression like this:

Replace([First Name] & "." & [Last Name], " ", ".") &
"CompanyName.com"


I have a bout 600 records of employee names in my tblEmployee. I have
there
first name as e.g., John A. and EmplLastName as e.g., Smith. I have
EmplEmail that is supposed to be e.g., [email protected]
Wherer
Company Name represents the company's name.

I would like to create an update query that will join the First Name
and
Last Name, add the period between John and A and if there is only one
name
provided as John then it should be John.Smith therefore add the period
after
John...

E.g., [email protected] ( if this is what was provided as
name
John A. Smith)
[email protected] (If this is what was provided as Name
Jane Shell)

I hope I have explained in detail, anyone please help!