Problem with Update query

Hi,

I have inhertied a database that is full of inconsistencies. I would
like to correct that. I know that I can apply a input mask but that
will be only for new records. I need to fix the existing data.

For example, with the home phone number some data is stored as
xxxxxxxxxx and others as (xxx) xxx-xxxx. I would like all the data to
be in the 2nd format but I am having some problem setting up the
update query.

table: tblNamelist
field: Home

The UPdate query is:
UPDATE tblNameList SET tblNameList.Home = "(" & Left([Home],3) & ") "
& Mid([Home],4,3) & "-" & Right([Home],4)
WHERE (((tblNameList.Home) Is Not Null));

Any help will be appreciated. Thanks is advance.

On Mon, 18 Jun 2007 18:51:44 -0700, 24t42 wrote:

Hi,

I have inhertied a database that is full of inconsistencies. I would
like to correct that. I know that I can apply a input mask but that
will be only for new records. I need to fix the existing data.

For example, with the home phone number some data is stored as
xxxxxxxxxx and others as (xxx) xxx-xxxx. I would like all the data to
be in the 2nd format but I am having some problem setting up the
update query.

I'd go the other way: set the phone field to all numbers, and use an Input
Mask to just display the punctuation. I'd be really leery about just inserting
parentheses - depending on the mixture of formats of the data now in the table
you could get a real mess!

Try updating Phone to

Replace(Replace(Replace(Replace([Phone], "(", ""), ")", ""), "-", ""), " ",
"")

to strip out all the special charaters; then run a query with a criterion

NOT LIKE "##########"

to find all records with a phone number that is NOT just ten digits. Fix these
manually or with another update query.

If you really want to store the parens, you can then update Phone to

"(" & Left([Phone], 3) & ") " & Mid([Phone], 4, 3) & "-" & Right([Phone], 4)

but I'd really suggest stripping it down to digits and checking for other
formats first!

John W. Vinson [MVP]

UPDATE tblNameList
SET tblNameList.Home = Format([Home],"(@@@) @@@-@@@@")
WHERE [Home] Not Like "##########" And [Home] is not null

WARNING: Backup your data first so you can recover if this does not work as
you expect.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"24t42" wrote in message
oups.com...
Hi,

I have inhertied a database that is full of inconsistencies. I would
like to correct that. I know that I can apply a input mask but that
will be only for new records. I need to fix the existing data.

For example, with the home phone number some data is stored as
xxxxxxxxxx and others as (xxx) xxx-xxxx. I would like all the data to
be in the 2nd format but I am having some problem setting up the
update query.

table: tblNamelist
field: Home

The UPdate query is:
UPDATE tblNameList SET tblNameList.Home = "(" & Left([Home],3) & ") "
& Mid([Home],4,3) & "-" & Right([Home],4)
WHERE (((tblNameList.Home) Is Not Null));

Any help will be appreciated. Thanks is advance.

ARGH!!! Posted wrong criteria

UPDATE tblNameList
SET tblNameList.Home = Format([Home],"(@@@) @@@-@@@@")
WHERE [Home] Like "##########"

WARNING: Backup your data first so you can recover if this does not work as
you expect.


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

"24t42" wrote in message
oups.com...
Hi,

I have inhertied a database that is full of inconsistencies. I would
like to correct that. I know that I can apply a input mask but that
will be only for new records. I need to fix the existing data.

For example, with the home phone number some data is stored as
xxxxxxxxxx and others as (xxx) xxx-xxxx. I would like all the data to
be in the 2nd format but I am having some problem setting up the
update query.

table: tblNamelist
field: Home

The UPdate query is:
UPDATE tblNameList SET tblNameList.Home = "(" & Left([Home],3) & ") "
& Mid([Home],4,3) & "-" & Right([Home],4)
WHERE (((tblNameList.Home) Is Not Null));

Any help will be appreciated. Thanks is advance.

Thank you both for the replies. I tried the update query:

UPDATE tblNameList
SET tblNameList.Home = Format([Home],"(@@@) @@@-@@@@")
WHERE [Home] Like "##########"

but that did not change the data. ANy other suggestions?



On Jun 19, 7:19 am, "John Spencer" wrote:
ARGH!!! Posted wrong criteria

UPDATE tblNameList
SET tblNameList.Home = Format([Home],"(@@@) @@@-@@@@")
WHERE [Home] Like "##########"

WARNING: Backup your data first so you can recover if this does not work as
you expect.

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

"24t42" wrote in message

oups.com...

Hi,

I have inhertied a database that is full of inconsistencies. I would
like to correct that. I know that I can apply a input mask but that
will be only for new records. I need to fix the existing data.

For example, with the home phone number some data is stored as
xxxxxxxxxx and others as (xxx) xxx-xxxx. I would like all the data to
be in the 2nd format but I am having some problem setting up the
update query.

table: tblNamelist
field: Home

The UPdate query is:
UPDATE tblNameList SET tblNameList.Home = "(" & Left([Home],3) & ") "
& Mid([Home],4,3) & "-" & Right([Home],4)
WHERE (((tblNameList.Home) Is Not Null));

Any help will be appreciated. Thanks is advance.

Did you execute the query or did you just switch to the datasheet view? If
you just switch to datasheet view then the query only shows you what it WILL
update if you run the query.

If you are in design view, select Query: Run from the menu. (Or press the
Red exclamation button on the menubar).

If you did run the query then try changing the where clause to the following
and see if that works (10 question marks/10 underscore characters)

WHERE [Home] Like "??????????" or [Home] Like "__________"

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

"24t42" wrote in message
ps.com...
Thank you both for the replies. I tried the update query:

UPDATE tblNameList
SET tblNameList.Home = Format([Home],"(@@@) @@@-@@@@")
WHERE [Home] Like "##########"

but that did not change the data. ANy other suggestions?



On Jun 19, 7:19 am, "John Spencer" wrote:
ARGH!!! Posted wrong criteria

UPDATE tblNameList
SET tblNameList.Home = Format([Home],"(@@@) @@@-@@@@")
WHERE [Home] Like "##########"

WARNING: Backup your data first so you can recover if this does not work
as
you expect.

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

"24t42" wrote in message

oups.com...

Hi,

I have inhertied a database that is full of inconsistencies. I would
like to correct that. I know that I can apply a input mask but that
will be only for new records. I need to fix the existing data.

For example, with the home phone number some data is stored as
xxxxxxxxxx and others as (xxx) xxx-xxxx. I would like all the data to
be in the 2nd format but I am having some problem setting up the
update query.

table: tblNamelist
field: Home

The UPdate query is:
UPDATE tblNameList SET tblNameList.Home = "(" & Left([Home],3) & ") "
& Mid([Home],4,3) & "-" & Right([Home],4)
WHERE (((tblNameList.Home) Is Not Null));

Any help will be appreciated. Thanks is advance.

John,

I do appreciate your help. I finally got it to work. I did not notice
that I was getting a Security Alert message. As soon as I addressed
that issue, your update query worked.

Thanks again.



On Jun 19, 3:18 pm, "John Spencer" wrote:
Did you execute the query or did you just switch to the datasheet view? If
you just switch to datasheet view then the query only shows you what it WILL
update if you run the query.

If you are in design view, select Query: Run from the menu. (Or press the
Red exclamation button on the menubar).

If you did run the query then try changing the where clause to the following
and see if that works (10 question marks/10 underscore characters)

WHERE [Home] Like "??????????" or [Home] Like "__________"

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

"24t42" wrote in message

ps.com...

Thank you both for the replies. I tried the update query:

UPDATE tblNameList
SET tblNameList.Home = Format([Home],"(@@@) @@@-@@@@")
WHERE [Home] Like "##########"

but that did not change the data. ANy other suggestions?

On Jun 19, 7:19 am, "John Spencer" wrote:
ARGH!!! Posted wrong criteria

UPDATE tblNameList
SET tblNameList.Home = Format([Home],"(@@@) @@@-@@@@")
WHERE [Home] Like "##########"

WARNING: Backup your data first so you can recover if this does not work
as
you expect.

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

"24t42" wrote in message

groups.com...

Hi,

I have inhertied a database that is full of inconsistencies. I would
like to correct that. I know that I can apply a input mask but that
will be only for new records. I need to fix the existing data.

For example, with the home phone number some data is stored as
xxxxxxxxxx and others as (xxx) xxx-xxxx. I would like all the data to
be in the 2nd format but I am having some problem setting up the
update query.

table: tblNamelist
field: Home

The UPdate query is:
UPDATE tblNameList SET tblNameList.Home = "(" & Left([Home],3) & ") "
& Mid([Home],4,3) & "-" & Right([Home],4)
WHERE (((tblNameList.Home) Is Not Null));

Any help will be appreciated. Thanks is advance.