Can't see old phone numbers

I have a table with both imported data and new records. The table contains a
phone number and is formatted as follows (xxx) xxx-xxxx. If I try to "filter
by form" on the phone number all new records work just fine. In all old
imported records the phone numbers are invisible to the filter. In both the
table and the form the phone number is in the same format and is displayed
the same.

I tried an Update Query as follows:

Update client set client.PhoneNumber = "(" & left([PhoneNumber],3)
& ") " & mid([PhoneNumber],4,3) & "-" & Right([PhoneNumber],4);

but this wiped out all the phone numbers replacing them with a single 0.

I recovered the phone numbers by going to my backup copy. (Yep, some of us
actually back up our data prior to attempting major changes!! )

Anyone have any ideas how I can update all the old numbers automatically
(programatically). If I retype the old phone numbers in manually the filter
is then able to find then. I just hate the idea of reentering all those old
phone numbers manually.

Thanks

I have sometimes copied entire tables into Excel, inserted working columns,
manipulated the data, removed the temp columns and pasted it all back into
the Access table. (Copies of the table made in Access beforehand of
course!)

Mich

"rleblanc" noone@somewhere wrote in message
...
I have a table with both imported data and new records. The table contains
a
phone number and is formatted as follows (xxx) xxx-xxxx. If I try to
"filter
by form" on the phone number all new records work just fine. In all old
imported records the phone numbers are invisible to the filter. In both
the
table and the form the phone number is in the same format and is
displayed
the same.

I tried an Update Query as follows:

Update client set client.PhoneNumber = "(" & left([PhoneNumber],3)
& ") " & mid([PhoneNumber],4,3) & "-" & Right([PhoneNumber],4);

but this wiped out all the phone numbers replacing them with a single 0.

I recovered the phone numbers by going to my backup copy. (Yep, some of us
actually back up our data prior to attempting major changes!! )

Anyone have any ideas how I can update all the old numbers automatically
(programatically). If I retype the old phone numbers in manually the
filter
is then able to find then. I just hate the idea of reentering all those
old
phone numbers manually.

Thanks

That won't work because I would still end up changing over 5000 phone
numbers by hand in the Excel spreadsheet. I need a small program of some
kind that will copy each individual number and then rewrite it back to where
it came from. Otherwise I'll have to do it manually which would be a BIG
pain!

Anyone else have any ideas?


"M Skabialka" wrote in message
...
I have sometimes copied entire tables into Excel, inserted working
columns,
manipulated the data, removed the temp columns and pasted it all back into
the Access table. (Copies of the table made in Access beforehand of
course!)

Mich

"rleblanc" noone@somewhere wrote in message
...
I have a table with both imported data and new records. The table
contains
a
phone number and is formatted as follows (xxx) xxx-xxxx. If I try to
"filter
by form" on the phone number all new records work just fine. In all old
imported records the phone numbers are invisible to the filter. In both
the
table and the form the phone number is in the same format and is
displayed
the same.

I tried an Update Query as follows:

Update client set client.PhoneNumber = "(" & left([PhoneNumber],3)
& ") " & mid([PhoneNumber],4,3) & "-" & Right([PhoneNumber],4);

but this wiped out all the phone numbers replacing them with a single 0.

I recovered the phone numbers by going to my backup copy. (Yep, some of
us
actually back up our data prior to attempting major changes!! )

Anyone have any ideas how I can update all the old numbers automatically
(programatically). If I retype the old phone numbers in manually the
filter
is then able to find then. I just hate the idea of reentering all those
old
phone numbers manually.

Thanks

On Fri, 27 Aug 2004 09:07:25 -0400, "rleblanc" noone@somewhere
wrote:

I have a table with both imported data and new records. The table contains a
phone number and is formatted as follows (xxx) xxx-xxxx. If I try to "filter
by form" on the phone number all new records work just fine. In all old
imported records the phone numbers are invisible to the filter. In both the
table and the form the phone number is in the same format and is displayed
the same.

I suspect the problem is that you have an input mask on the phone
field - and that the PhoneNumaber *for some records* is actually
stored like 4145551212 while the mask (or the Format) is causing it to
be displayed as (414) 555-1212. What in fact is the Mask? Or do you
have a Lookup to a Phones table? For that matter, what is the datatype
of the PhoneNumber field?

I tried an Update Query as follows:

Update client set client.PhoneNumber = "(" & left([PhoneNumber],3)
& ") " & mid([PhoneNumber],4,3) & "-" & Right([PhoneNumber],4);

Ought to have worked, assuming that the imported text is all digits
and it's a 12-byte Text field.

but this wiped out all the phone numbers replacing them with a single 0.

:-{(

I recovered the phone numbers by going to my backup copy. (Yep, some of us
actually back up our data prior to attempting major changes!! )

rleblanc gets a gold star! bg

Anyone have any ideas how I can update all the old numbers automatically
(programatically). If I retype the old phone numbers in manually the filter
is then able to find then. I just hate the idea of reentering all those old
phone numbers manually.


The Filter should (of course) match the actual contents. I'd suggest
storing just the digits, using an Input Mask to display the
punctuation (assuming that you will NEVER EVER have any phone numbers
outside North America, which have different formats), and use the
exact same input mask on the form control used to create the filter.
Or, use the ;0;_ suffix on the input mask and both store and search
for the full number with all punctuation.

John W. Vinson[MVP]
(no longer chatting for now)

Format is the phone number format as specified by Access. Type is not given
but is 25 characters long thus probably making it a string to Access. If I
manually reenter the phone number the filter lookup then works. What I need
is a routine to loop through the phone numbers, copy each one, and rewrite
it back to where it just came from. I thought the routine below would work
but it didn't. There must be some way to reenter most of the phone numbers
programmatically so that I don't have to reenter each individual phone
number manually!? Ugh!!!


"John Vinson" wrote in message
...
On Fri, 27 Aug 2004 09:07:25 -0400, "rleblanc" noone@somewhere
wrote:

I have a table with both imported data and new records. The table
contains a
phone number and is formatted as follows (xxx) xxx-xxxx. If I try to
"filter
by form" on the phone number all new records work just fine. In all old
imported records the phone numbers are invisible to the filter. In both
the
table and the form the phone number is in the same format and is
displayed
the same.

I suspect the problem is that you have an input mask on the phone
field - and that the PhoneNumaber *for some records* is actually
stored like 4145551212 while the mask (or the Format) is causing it to
be displayed as (414) 555-1212. What in fact is the Mask? Or do you
have a Lookup to a Phones table? For that matter, what is the datatype
of the PhoneNumber field?

I tried an Update Query as follows:

Update client set client.PhoneNumber = "(" & left([PhoneNumber],3)
& ") " & mid([PhoneNumber],4,3) & "-" & Right([PhoneNumber],4);

Ought to have worked, assuming that the imported text is all digits
and it's a 12-byte Text field.

but this wiped out all the phone numbers replacing them with a single 0.

:-{(

I recovered the phone numbers by going to my backup copy. (Yep, some of
us
actually back up our data prior to attempting major changes!! )

rleblanc gets a gold star! bg

Anyone have any ideas how I can update all the old numbers automatically
(programatically). If I retype the old phone numbers in manually the
filter
is then able to find then. I just hate the idea of reentering all those
old
phone numbers manually.


The Filter should (of course) match the actual contents. I'd suggest
storing just the digits, using an Input Mask to display the
punctuation (assuming that you will NEVER EVER have any phone numbers
outside North America, which have different formats), and use the
exact same input mask on the form control used to create the filter.
Or, use the ;0;_ suffix on the input mask and both store and search
for the full number with all punctuation.

John W. Vinson[MVP]
(no longer chatting for now)