If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Problem with Update query
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] |
#3
|
|||
|
|||
Problem with Update query
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. |
#4
|
|||
|
|||
Problem with Update query
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. |
#5
|
|||
|
|||
Problem with Update query
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. |
#6
|
|||
|
|||
Problem with Update query
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. |
#7
|
|||
|
|||
Problem with Update query
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. |
Thread Tools | |
Display Modes | |
|
|