Query Update Question

  • Thread starter Thread starter DukeArgos
  • Start date Start date
D

DukeArgos

I'm using Access 2002

I have data contained "within" a field that i want to update.

Example -- i have "," (a comma) that i want to change to ";"
(semicolon).
also i have " " (two spaces) that i want to change to " " (one
space).

data example:
Doe, John -- i want this to be == Doe; John
also
123 Main St -- i want this to be == 123 Main St

Does anyone know of a command that can do this all at once without
having to search for these instances individually?

thanks for the help!!! :)
 
Here are 2 approaches:

1) Open your table in datasheet mode. On the main menu invoke Edit>Replace.
Using the dialog box you can determine which field to target, whether it
looks for matches in part of or over the entire field, etc. You can also do
replacements as a batch or one-by-one.

2) Create a new query on the table. Make it an update query. Select one of
the fields you want to edit. Under "Update To" type use the Replace
function: Replace([PersonName],",",";"). Run the query. Change the selected
field to the other field. Change the function to Replace([Address]," ","
") . Run the query. The arguments for replace a

It's always a good idea to back up your data before operations like this,
just in case anything blows up in your face.

Hope this helps,

--
George Nicholson

Remove 'Junk' from return address.

************************************************
Here is part of the entry for the Replace function from VB Help, which can
be used within a query:

Description: Returns a string in which a specified substring has been
replaced with another substring a specified number of times.

Syntax: Replace(expression, find, replace[, start[, count[, compare]]])

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing substring to
replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where
substring search is to begin. If omitted, 1 is assumed.
count Optional. Number of substring substitutions to
perform. If omitted, the default value is -1, which means make all possible
substitutions.
compare Optional. Numeric value indicating the kind of
comparison to use when evaluating substrings..
***************************************************
 
Back
Top