Re: Editing Access Data

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <fb@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0109AC65-D502-4A58-990F-2E0B6D5F4114@xxxxxxxxxxxxxxxx
> 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
>
>

.

Relevant Pages

  • Re: Editing Access Data
    ... > second query to an update query, and update both your phone field and your ... > last name, etc.), so why not combine that exercise with the "moving phone ... >> actual name is sometimes, also appended with a ten digit phone ...
    (microsoft.public.access.queries)
  • Re: Insert a LIST into each line of 2nd file - WILL PAY $$$!
    ... the query edit window called "criteria". ... Being that one file has one field which is a 4 digit number ... I know I have more than sufficient disc space. ... I can avoid this part by simply removing records after the fact but ...
    (comp.databases.ms-access)
  • Re: Need Help with Criteria Expression
    ... query that will do this and save this query. ... setting a validation rule that's commensurate with the way data would ...  If I actually enter a space, that is the same as entering a digit so it ... This input mask will allow for 1 digit months, 2 digit days, and 2 digit ...
    (microsoft.public.access.queries)
  • Re: Updating Multiple records based on values
    ... The SQL statement for the update query might ... digit code is trimmed and then run a special SQL update to set the ... State field correctly, but I haven't been able to get it working. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query for Left(String,5) - Access 2007
    ... Note that this query cannot use the query design view, but must be done in the SQL view. ... FROM ((boston INNER JOIN Name ON boston.Boston = Name.ZIP) INNER JOIN ... The only time I've used like is in the criteral when I'n only using the nameaddress table and then I'd type in all the likes manually - though usually there are only 4 or 5 zips. ... It will then find all matching 5 digits and all that start with the 5 digit code. ...
    (microsoft.public.access.queries)