Break apart data and then concatenate it back together

[Gina]

I know how to get the length of a given field from a query... ie below. This
gives me the last 2 digits in a year.

Year: Right([tbl_Closures1.Date_Process_Started],2)

What I need to do (this is something separate but related) is separate a
field with both first and last names to two separate fields. I then need to
concatenate them together with a third field that holds the data for an email
extension ie. @abc.com. My end goal is to get an email address for 120
employees (without typing them individually).

The standard is (e-mail address removed). Everything past the @
sign is the same for every employee..

I thought of using the above script - but I don't know how to tell it to
stop at a space and also start at the space in order to capture first name
and then last name.

I also do not know how to concatenate in access.

Thank you in advance, your help is always appreciated.

 

[Jerry Whittle]

Look into the Replace function to replace the space with a period.

Things to think about: Do you have anyone named something like Mary Ann
Gilligan or Billy Bob Thorton? What does your company do if there are two
John Smith's?

 

[Gina]

Jerry,

That's excellent thinking... I complicate simple things at times. One more
thing... How do you get the replace function to recognize that you are
wanting it to look for a space. I tried an acutal space, also " " and the #
sign.????
--
Gina

Look into the Replace function to replace the space with a period.

Things to think about: Do you have anyone named something like Mary Ann
Gilligan or Billy Bob Thorton? What does your company do if there are two
John Smith's?
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I know how to get the length of a given field from a query... ie below. This
gives me the last 2 digits in a year.

Year: Right([tbl_Closures1.Date_Process_Started],2)

What I need to do (this is something separate but related) is separate a
field with both first and last names to two separate fields. I then need to
concatenate them together with a third field that holds the data for an email
extension ie. @abc.com. My end goal is to get an email address for 120
employees (without typing them individually).

The standard is (e-mail address removed). Everything past the @
sign is the same for every employee..

I thought of using the above script - but I don't know how to tell it to
stop at a space and also start at the space in order to capture first name
and then last name.

I also do not know how to concatenate in access.

Thank you in advance, your help is always appreciated.

 

[BruceM]

For the date you could just use:
TwoDigitYear: Format([YourDateField],"yy")
Year is the name of a function, so it should not be used as a field name.

For parsing names, some information here could give you a starting place:
http://www.mvps.org/access/strings/str0001.htm

To put them back together you could use an update query. Make an e-mail
field, then update it to something like:
LCase([FirstName]) & "." & LCase([LastName]) & "@emailaddress.com"

As Jerry pointed out, you could use the Replace function to set up the
e-mail address, which would work too. I am assuming you want to keep the
first and last names separate. Jerry pointed out the difficulties that will
arise from certain names such as Billy Bob Thornton, but I assume you would
just take care of things like that manually considering that you are dealing
with a relatively short list of names.

By the way, you may receive the suggestion that you could just concatenate
the e-mail addresses on the fly, but that won't work in some cases such as
Billy Bob or a duplicate name. Also, if somebody's name changes you may not
be be certain the e-mail address will change at the same time.

 

[Gina]

Jerry and Bruce,

Thank you both. The information you have supplied has proven very valuable
to me. Although this is a short list, the next time it might prove to be in
the 10's of thousands. As our DB is reaching 100,000 records.

Again, thank you both for your time and knowledge.

 

[Jerry Whittle]

Something like this should work:

Debug.Print Replace("jerry whittle"," ",".")

The space should be in the second set of quotes and the period in the last.
Put the field name in the the first position enclosed by square brackets [ ]
without the double-quotes.

Happy to help.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry,

That's excellent thinking... I complicate simple things at times. One more
thing... How do you get the replace function to recognize that you are
wanting it to look for a space. I tried an acutal space, also " " and the #
sign.????
--
Gina

Look into the Replace function to replace the space with a period.

Things to think about: Do you have anyone named something like Mary Ann
Gilligan or Billy Bob Thorton? What does your company do if there are two
John Smith's?
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I know how to get the length of a given field from a query... ie below. This
gives me the last 2 digits in a year.

Year: Right([tbl_Closures1.Date_Process_Started],2)

What I need to do (this is something separate but related) is separate a
field with both first and last names to two separate fields. I then need to
concatenate them together with a third field that holds the data for an email
extension ie. @abc.com. My end goal is to get an email address for 120
employees (without typing them individually).

The standard is (e-mail address removed). Everything past the @
sign is the same for every employee..

I thought of using the above script - but I don't know how to tell it to
stop at a space and also start at the space in order to capture first name
and then last name.

I also do not know how to concatenate in access.

Thank you in advance, your help is always appreciated.

 

[BruceM]

When you say the DB is reaching 100,000 records, do you mean there are to be
100,000 separate names? If the same address appears in many records you may
need to take a look at your database's structure.

Jerry and Bruce,

Thank you both. The information you have supplied has proven very
valuable
to me. Although this is a short list, the next time it might prove to be
in
the 10's of thousands. As our DB is reaching 100,000 records.

Again, thank you both for your time and knowledge.

--
Gina

I know how to get the length of a given field from a query... ie below.
This
gives me the last 2 digits in a year.

Year: Right([tbl_Closures1.Date_Process_Started],2)

What I need to do (this is something separate but related) is separate a
field with both first and last names to two separate fields. I then need
to
concatenate them together with a third field that holds the data for an
email
extension ie. @abc.com. My end goal is to get an email address for 120
employees (without typing them individually).

The standard is (e-mail address removed). Everything past the
@
sign is the same for every employee..

I thought of using the above script - but I don't know how to tell it to
stop at a space and also start at the space in order to capture first
name
and then last name.

I also do not know how to concatenate in access.

Thank you in advance, your help is always appreciated.