Re: Editing Access Data

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" wrote:

> 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: To change negative to positive in MS Access
    ... If you want to use the UPDATE query method and you will be importing new data into the table, ... UPDATE query that will only change the numbers that haven't been changed. ... changed data or if it is new, unchanged data and then build your UPDATE ...
    (microsoft.public.access.queries)
  • Re: Shifting textboxes on report after hidden
    ... Are all of these in the same report ... section of the report and these textboxes are located in the "Exercise ... but am not quite sure how to set up the query to resolve ... This is the code I am using for the checkboxes, ...
    (microsoft.public.access.reports)
  • Re: Update query after an append query?+
    ... computer so that I could tell when and if I get the update query to work. ... that if you created your query under a different ANSI SQL query mode than ... the current mode of your Microsoft Access database then your update query ...
    (microsoft.public.access.formscoding)
  • Re: Append import data to existing record
    ... I'm glad you've got the key query working. ... With the update query open in Design View: ... Split the filespec apart and reassemble it into a ... Function BuildJetTextSource(ByVal FileSpec As String, ...
    (microsoft.public.access.externaldata)