Replace Text Based on Variable Search

[Sharkbyte]

I have a db that has some older records that contain SSNs in the text. I can
serch the record, to find the records needing to be updated, but I don't know
how to write the Replace function.

My search string is: Like "*#########*" or Like "*###-##-####*"

Could someone help me with the code to update based on this criteria?

Thanks.

Sharkbyte

 

[John W. Vinson]

I have a db that has some older records that contain SSNs in the text. I can
serch the record, to find the records needing to be updated, but I don't know
how to write the Replace function.

My search string is: Like "*#########*" or Like "*###-##-####*"

Could someone help me with the code to update based on this criteria?

Thanks.

Sharkbyte

What do you want to replace, and with what? This is, I take it, a long text
(or Memo?) field containing a SSN embedded among other text? Do you want to
replace the SSN with another SSN, or with something else?

More info please!

 

[Sharkbyte]

Sorry John. It doesn't seem to me that the code would be that much different
whether I want to replace the number with x's (xxx-xx-xxxx) or simply with
"". I understand the Replace statement, I just can't figure the coding to
capture the SSN, and process it.

I'm searching text and memo fields for SSNs, hence the Like statements. I
am looking to replace/remove - the SSN only - from the text.

Thanks.

 

[John W. Vinson]

Sorry John. It doesn't seem to me that the code would be that much different
whether I want to replace the number with x's (xxx-xx-xxxx) or simply with
"". I understand the Replace statement, I just can't figure the coding to
capture the SSN, and process it.

I'm searching text and memo fields for SSNs, hence the Like statements. I
am looking to replace/remove - the SSN only - from the text.

That actually isn't trivial, since the Replace statement requires a literal
text string to be replaced. It doesn't accept wildcards. I suspect you'll need
to use custom VBA code to parse through the string finding sequences of
digits.

This could be a real problem if there are OTHER digit strings in the code!

I'd suggest doing a search for "Regular Expressions", that's just what's
needed in this case. I've got some other work to do but I'll leave this thread
live and post back in a bit with some possible code to do it.

 

[Sharkbyte]

I'd suggest doing a search for "Regular Expressions", that's just what's
needed in this case. I've got some other work to do but I'll leave this thread
live and post back in a bit with some possible code to do it.
--

John:

What about the possibility, of when there is a match, capturing the value
into a variable? Then run the Replace command against that field, for the
value stored in the variable.

 

[John W. Vinson]

John:

What about the possibility, of when there is a match, capturing the value
into a variable? Then run the Replace command against that field, for the
value stored in the variable.

I was trying to think how, but it's not obvious without using Regular
Expressions. You can't find the position of an imprecisely defined string
using InStr, for example. All I can suggest is VBA code which would step
through the string byte by byte looking for strings of numbers.

 

[Sharkbyte]

I was trying to think how, but it's not obvious without using Regular
Expressions. You can't find the position of an imprecisely defined string
using InStr, for example. All I can suggest is VBA code which would step
through the string byte by byte looking for strings of numbers.

Can you offer some code suggestions on how to do this? I could then run a
generic search, to isolate the group of records, which include matches, then
use your more specific code, to go into the particular record and search out
the specific matches, and remove/replace the data.

 

[De Jager]

I have a db that has some older records that contain SSNs in the text. I
can
serch the record, to find the records needing to be updated, but I don't
know
how to write the Replace function.

My search string is: Like "*#########*" or Like "*###-##-####*"

Could someone help me with the code to update based on this criteria?

Thanks.

Sharkbyte

 

[joelgeraldine]

jkjkjkjk