Deleting Digits from numbers

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

Guest

How do you delete the last 2 digits from numbers? Some of the phone numbers
in my contact list happen to contain 2 extra digits at the end that I would
like to delete. How would I do this without affecting the correct numbers?
 
Are they phone numbers with extra digits longer than all the ones
without the extra digits? How many characters do the have?

UPDATE YourTable
SET Telephone = Left([Telephone],10)
WHERE Len(Telephone) > 10

If the correct length is more than 10 then change 10 to 12 or 14
For example.
4104559999 (10 characters)
410.455.9999 (12 characters)
(410) 455-9999 (14 Characters)

BACKUP your data first.

IF you are using the query view (query grid),
-- Select Query: Update from the menu
-- Set the grid up as follows

Field: Telephone
Table: YourTable
Update: Left([Telephone],14)
Criteria: Len(Telephone) > 14



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
How do you delete the last 2 digits from numbers? Some of the phone numbers
in my contact list happen to contain 2 extra digits at the end that I would
like to delete. How would I do this without affecting the correct numbers?
Click to expand...

Well, you need some way to determine which phone numbers have extra digits and
which don't. Not being able to see your data, I don't know what that might be!

I'd say BACK UP YOUR DATABASE (you cannot undo update queries); create a query
based on your table, and use a criterion to identify the numbers to be edited.
For instance, if your phone numbers are mostly like

5551231234

but some are like

555123123488

and you want to get rid of the 88, you could put a calculated field in the
query

Len([Phone])

and use a criterion of 12 on it (to select all the 12-byte values).

Update the field to

Left([Phone], 10)

or

Left([Phone], Len([Phone]) - 2)

if the length is in fact variable.

John W. Vinson [MVP]
 
Thank you, Thank you. As usual, your responses are excellent and very
helpful. When I get the chance in the next day or two, I will put this to
work. By the way, your responses on removing the formatting from phone
numbers were magnificent and worked very well. Thanks again.

John Spencer said:
Are they phone numbers with extra digits longer than all the ones
without the extra digits? How many characters do the have?

UPDATE YourTable
SET Telephone = Left([Telephone],10)
WHERE Len(Telephone) > 10

If the correct length is more than 10 then change 10 to 12 or 14
For example.
4104559999 (10 characters)
410.455.9999 (12 characters)
(410) 455-9999 (14 Characters)

BACKUP your data first.

IF you are using the query view (query grid),
-- Select Query: Update from the menu
-- Set the grid up as follows

Field: Telephone
Table: YourTable
Update: Left([Telephone],14)
Criteria: Len(Telephone) > 14



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

How do you delete the last 2 digits from numbers? Some of the phone numbers
in my contact list happen to contain 2 extra digits at the end that I would
like to delete. How would I do this without affecting the correct numbers?
Click to expand...
Click to expand...
 
This data is imported from the web. Some web sites happen to put two extra
zeros on the phone numbers that need to be removed. In my case, I update all
the phone numbers with a 1, so they have 11 digits. That makes these numbers
have 13 digits

John Spencer said:
Are they phone numbers with extra digits longer than all the ones
without the extra digits? How many characters do the have?

UPDATE YourTable
SET Telephone = Left([Telephone],10)
WHERE Len(Telephone) > 10

If the correct length is more than 10 then change 10 to 12 or 14
For example.
4104559999 (10 characters)
410.455.9999 (12 characters)
(410) 455-9999 (14 Characters)

BACKUP your data first.

IF you are using the query view (query grid),
-- Select Query: Update from the menu
-- Set the grid up as follows

Field: Telephone
Table: YourTable
Update: Left([Telephone],14)
Criteria: Len(Telephone) > 14



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

How do you delete the last 2 digits from numbers? Some of the phone numbers
in my contact list happen to contain 2 extra digits at the end that I would
like to delete. How would I do this without affecting the correct numbers?
Click to expand...
Click to expand...
 
Back
Top