Find & Replace for a field

  • Thread starter Thread starter Erik
  • Start date Start date
E

Erik

Is there a way to set up a find and replace? I have a
table where there is 1 field that has the same masterID
number in 5 or more records. Is there a way to write a
code that will search that field in the table, find the
specified masterID and replace it with a user defined new
ID number?
 
Is there a way to write a
code that will search that field in the table, find the
specified masterID and replace it with a user defined new
ID number?
Click to expand...
That's what update queries are for:

update mytable
set MasterID = "something"
where masterID = "Something Else"

Hope that helps


Tim F
 
Yes, but what if you want to change only part of a field,
not the whole field. For example, replace any slash
character with a carriage return character, wherever the
slash character may be in the field.

Hope you can help. Thanks.
 
Yes, but what if you want to change only part of a field,
not the whole field. For example, replace any slash
character with a carriage return character, wherever the
slash character may be in the field.
Click to expand...

You can use the Replace() function if you have a late enough version of
Access, or else you can use a chunk of Instr() and Left() and Mid()
function.

If you post some details we may be able to help...

B Wishes

Tim F
 
I have a vb project that logs users' responses to a text
file. I import the text file to Access for analysis. I
have to allow the use of line breaks in the response form,
but when I import the data to Access (using the wizard),
Access begins a new record at each line break.

I thought I could use F&R in the text file to use a string
like "\\\" in place of the line breaks and change them
back to line breaks in Access. They import great, but you
can't use the carriage return character in Access F&R
dialog, so I thought I could do it programmatically. I
asked Access 2002 VBA help about a replace function, and
got this:

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

but there is no example code. (I stink at VB unless I have
a place from which to start.) I have 5 fields which may
contain the string to be replaced. I can use Chr (13) for
the replacement string, but how do I use the 5 fields as
my expression? What else do I need to know?

Is there another option? If I import the data thru code
instead of the wizard, can I get around CRLF's starting a
new record?

Thanks a million!
Erin
 
Replace(expression, find, replace[, start[, count[,
compare]]])

but there is no example code.
Click to expand...

I don't have A2002, and haven't used the Replace function, but it should be
something along the lines of:

UPDATE MyTable
SET MyField = Replace(MyField, "///", Chr(13), 1, yes, vbCompareText)
WHERE MyField IS NOT NULL

I think there is something in the Count parameter that makes it convert
every occurrence rather than just the first: you'll have to check in the
small print.

Hope that helps


Tim F
 
Back
Top