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
|
|||
|
|||
Update Query-- in reference to Find & Replace
John, thanks for the reply. The column name is "PAT RELATION TO INSD" The
fields are populated with a " X" or a " X" or a "" what I need to do is replace the first with "Self" the second with "Spouse" and the third with "Child". Will an update query work for this? and how? Thanks for all your help. |
#2
|
|||
|
|||
On Mon, 13 Dec 2004 14:05:05 -0800, "Chris"
wrote: John, thanks for the reply. The column name is "PAT RELATION TO INSD" The fields are populated with a " X" or a " X" or a "" what I need to do is replace the first with "Self" the second with "Spouse" and the third with "Child". Will an update query work for this? and how? Thanks for all your help. You'll need to use either three update queries in succession, or an upste query using a Switch() function call. The latter is less obvious but probably more efficient. Try: UPDATE yourtable SET [PAT RELATION TO INSD] = Switch([PAT RELATION TO INSD] = " X", "Self", [PAT RELATION TO INSD] = " X", "Spouse", [PAT RELATION TO INSD] IS NULL, "Child", True, "*ERROR*") The Switch function takes arguments in pairs; it loops through the arguments pairwise, and if the first member of the pair is TRUE it returns the second member and quits. The last line will return the text string *ERROR* if the column contains (say) " X" (wrong number of spaces) or other ambiguous or wrong data. If you want those values left un-updated, use [PAT RELATION TO INSD] instead of "*ERROR*" to update the field to itself. John W. Vinson[MVP] Join the online Access Chats Tuesday 11am EDT - Thursday 3:30pm EDT http://community.compuserve.com/msdevapps |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Find and Replace Query with Wildcards in Access | GoFlyersCS | Running & Setting Up Queries | 6 | October 18th, 2004 01:23 PM |
Update another table with a Max record query | Ngan | Running & Setting Up Queries | 2 | June 22nd, 2004 05:01 PM |
query field reference help | -dch | Running & Setting Up Queries | 4 | June 2nd, 2004 07:30 PM |
Find and Replace in Word 97 | Doug Robbins - Word MVP | General Discussion | 1 | May 28th, 2004 02:41 PM |