Update Query Question!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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., (e-mail address removed) 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., (e-mail address removed) ( if this is what was provided as name
John A. Smith)
(e-mail address removed) (If this is what was provided as Name
Jane Shell)

I hope I have explained in detail, anyone please help!
 
In your Update query, try an expression like this:

Replace([First Name] & "." & [Last Name], " ", ".") & "CompanyName.com"
 
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



Allen Browne said:
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.

JOM said:
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., (e-mail address removed)
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., (e-mail address removed) ( if this is what was provided as name
John A. Smith)
(e-mail address removed) (If this is what was provided as Name
Jane Shell)

I hope I have explained in detail, anyone please help!
Click to expand...
Click to expand...
 
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.

JOM said:
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



Allen Browne said:
In your Update query, try an expression like this:

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


JOM said:
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., (e-mail address removed)
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., (e-mail address removed) ( if this is what was provided as
name
John A. Smith)
(e-mail address removed) (If this is what was provided as Name
Jane Shell)

I hope I have explained in detail, anyone please help!
Click to expand...
Click to expand...
Click to expand...
 
Thanks, that worked perfectly well

Allen Browne said:
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.

JOM said:
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



Allen Browne said:
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., (e-mail address removed)
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., (e-mail address removed) ( if this is what was provided as
name
John A. Smith)
(e-mail address removed) (If this is what was provided as Name
Jane Shell)

I hope I have explained in detail, anyone please help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top