Editing Access Data

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

Guest

Hi:

I have an Access Database with a 'full name' field. Unfortunately, the
actual name (the data) is sometimes, also appended with a ten digit phone
number. Thus I have, in the name field, "full name phone". Phone is always a
number. There is also another field called "phone" where the phone number
should be placed.

I would like to extrapulate the phone number from the name field and place
it in the "phone" field, thus leaving the "name" field only with the full
name and the "phone" field with the phone number.

Any help how I can accomplish this, sample code would be appreciated. Thanks
 
So, you are saying that the [full name] field sometimes ends in a digit, but
that only happens when the phone number is added at the end of the field?

Is there any character (a colon, a semi-colon, a pair of dashes, ...) that
separates phone number form person name? If so, you could use the InStr()
function to locate that.

The basic approach is to first identify (ONLY) those records with a phone
number in them (see above), then run a query that returns a phone number
(and the [full name] & record ID) (see previous). Then you'd convert that
second query to an update query, and update both your phone field and your
person name field.

I suspect you'll want to break your [full name] field apart (for sorting by
last name, etc.), so why not combine that exercise with the "moving phone
numbers" exercise, and do it all?

P.S. This really depends on whether you have 5 rows like this or 5000 with
an embedded phone number. Just copy/paste in the former situation... <g>.
 
Jeff:

Thanks for the response. Yes, some of the data in the name field contains
the lastname, firstname (or Lastname first name) then a space and then the
phone number. I guess if I can check the last four digits of the full name
field for numeric that will be a candidate to work on. Then, how do I break
them apart, i.e. seperate the name from the phone number ?

thanks


Jeff Boyce said:
So, you are saying that the [full name] field sometimes ends in a digit, but
that only happens when the phone number is added at the end of the field?

Is there any character (a colon, a semi-colon, a pair of dashes, ...) that
separates phone number form person name? If so, you could use the InStr()
function to locate that.

The basic approach is to first identify (ONLY) those records with a phone
number in them (see above), then run a query that returns a phone number
(and the [full name] & record ID) (see previous). Then you'd convert that
second query to an update query, and update both your phone field and your
person name field.

I suspect you'll want to break your [full name] field apart (for sorting by
last name, etc.), so why not combine that exercise with the "moving phone
numbers" exercise, and do it all?

P.S. This really depends on whether you have 5 rows like this or 5000 with
an embedded phone number. Just copy/paste in the former situation... <g>.

--
Regards

Jeff Boyce
<Office/Access MVP>

fb said:
Hi:

I have an Access Database with a 'full name' field. Unfortunately, the
actual name (the data) is sometimes, also appended with a ten digit phone
number. Thus I have, in the name field, "full name phone". Phone is always a
number. There is also another field called "phone" where the phone number
should be placed.

I would like to extrapulate the phone number from the name field and place
it in the "phone" field, thus leaving the "name" field only with the full
name and the "phone" field with the phone number.

Any help how I can accomplish this, sample code would be appreciated. Thanks
Click to expand...
Click to expand...
 
Check InStr(), Left(), Mid() and Right() functions in Access HELP. You'd
combine these in a query to "get" the pieces you'd need. Do it as a select
query to start with, until you are confident that the query is getting what
you need. Then you can look into doing an update (change the query to an
update query), if that's what you decide to do.

--
Regards

Jeff Boyce
<Office/Access MVP>

fb said:
Jeff:

Thanks for the response. Yes, some of the data in the name field contains
the lastname, firstname (or Lastname first name) then a space and then the
phone number. I guess if I can check the last four digits of the full name
field for numeric that will be a candidate to work on. Then, how do I break
them apart, i.e. seperate the name from the phone number ?

thanks


Jeff Boyce said:
So, you are saying that the [full name] field sometimes ends in a digit, but
that only happens when the phone number is added at the end of the field?

Is there any character (a colon, a semi-colon, a pair of dashes, ...) that
separates phone number form person name? If so, you could use the InStr()
function to locate that.

The basic approach is to first identify (ONLY) those records with a phone
number in them (see above), then run a query that returns a phone number
(and the [full name] & record ID) (see previous). Then you'd convert that
second query to an update query, and update both your phone field and your
person name field.

I suspect you'll want to break your [full name] field apart (for sorting by
last name, etc.), so why not combine that exercise with the "moving phone
numbers" exercise, and do it all?

P.S. This really depends on whether you have 5 rows like this or 5000 with
an embedded phone number. Just copy/paste in the former situation...
Click to expand...
--
Regards

Jeff Boyce
<Office/Access MVP>

always
a
Click to expand...
Click to expand...
 
Thanks. Appreciate it.

Jeff Boyce said:
Check InStr(), Left(), Mid() and Right() functions in Access HELP. You'd
combine these in a query to "get" the pieces you'd need. Do it as a select
query to start with, until you are confident that the query is getting what
you need. Then you can look into doing an update (change the query to an
update query), if that's what you decide to do.

--
Regards

Jeff Boyce
<Office/Access MVP>

fb said:
Jeff:

Thanks for the response. Yes, some of the data in the name field contains
the lastname, firstname (or Lastname first name) then a space and then the
phone number. I guess if I can check the last four digits of the full name
field for numeric that will be a candidate to work on. Then, how do I break
them apart, i.e. seperate the name from the phone number ?

thanks


Jeff Boyce said:
So, you are saying that the [full name] field sometimes ends in a digit, but
that only happens when the phone number is added at the end of the field?

Is there any character (a colon, a semi-colon, a pair of dashes, ...) that
separates phone number form person name? If so, you could use the InStr()
function to locate that.

The basic approach is to first identify (ONLY) those records with a phone
number in them (see above), then run a query that returns a phone number
(and the [full name] & record ID) (see previous). Then you'd convert that
second query to an update query, and update both your phone field and your
person name field.

I suspect you'll want to break your [full name] field apart (for sorting by
last name, etc.), so why not combine that exercise with the "moving phone
numbers" exercise, and do it all?

P.S. This really depends on whether you have 5 rows like this or 5000 with
an embedded phone number. Just copy/paste in the former situation...
Click to expand...
--
Regards

Jeff Boyce
<Office/Access MVP>

Hi:

I have an Access Database with a 'full name' field. Unfortunately, the
actual name (the data) is sometimes, also appended with a ten digit phone
number. Thus I have, in the name field, "full name phone". Phone is always
a
number. There is also another field called "phone" where the phone number
should be placed.

I would like to extrapulate the phone number from the name field and place
it in the "phone" field, thus leaving the "name" field only with the full
name and the "phone" field with the phone number.

Any help how I can accomplish this, sample code would be appreciated.
Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top