Using Replace All when dealing with special characters

[exbelgieswig]

Hello,

I am trying to use the replace all function to replace a string of next with
a number. This works fine when there are no special characters but not when
special characters are present.

For example:

Trying to Replace " Internal Reconciliation [Post-Impact] - Manual, All Data "
with the number " 33 "
yields the error message " The pattern string is invalid "

The dashes and parentheses seem to be gumming up the works. Is there anyway
I can work around this so that I can replace my text fields en masse with
numerals?

Thanks

 

[Douglas J. Steele]

Actually, I believe it's only the square brackets.

See whether replacing " Internal Reconciliation [[]Post-Impact[]] - Manual,
All Data " works any better. (That's square brackets around each of the
square brackets)

 

[exbelgieswig]

Thanks Doug, that seemed to do the trick. (Sorry to get theoretical on you),
why does Access require the brackets?

Actually, I believe it's only the square brackets.

See whether replacing " Internal Reconciliation [[]Post-Impact[]] - Manual,
All Data " works any better. (That's square brackets around each of the
square brackets)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,

I am trying to use the replace all function to replace a string of next
with
a number. This works fine when there are no special characters but not
when
special characters are present.

For example:

Trying to Replace " Internal Reconciliation [Post-Impact] - Manual, All
Data "
with the number " 33 "
yields the error message " The pattern string is invalid "

The dashes and parentheses seem to be gumming up the works. Is there
anyway
I can work around this so that I can replace my text fields en masse with
numerals?

Thanks

 

[Douglas J. Steele]

To be honest, I'm not sure why it should make a difference in Replace, but
there are a number of special characters used by the Like operator.

With Like, you can put a number of characters inside square brackets, and
it'll call it a match if one of those characters is in the string. For
example, Like 'ca[dt]' would match cad and cat, but not cap or car. You can
also put a ! in there, meaning anything but those characterse Like 'ca[!dt]'
would NOT match cad and cat, but would match cap, car, can, etc.

As a result, the Help file (entry for Like Operator) notes: "To match the
special characters left bracket ([), question mark (?), number sign (#), and
asterisk (*), enclose them in brackets. The right bracket (]) can't be used
within a group to match itself, but it can be used outside a group as an
individual character."

Now, that sort of contradicts the pattern I gave you. It implies that

" Internal Reconciliation [[]Post-Impact[]] - Manual, All Data "

shouldn't work (because of the []]), and that

" Internal Reconciliation [[]Post-Impact] - Manual, All Data "

should be all you need.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug, that seemed to do the trick. (Sorry to get theoretical on
you),
why does Access require the brackets?

Actually, I believe it's only the square brackets.

See whether replacing " Internal Reconciliation [[]Post-Impact[]] -
Manual,
All Data " works any better. (That's square brackets around each of the
square brackets)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello,

I am trying to use the replace all function to replace a string of next
with
a number. This works fine when there are no special characters but not
when
special characters are present.

For example:

Trying to Replace " Internal Reconciliation [Post-Impact] - Manual, All
Data "
with the number " 33 "
yields the error message " The pattern string is invalid "

The dashes and parentheses seem to be gumming up the works. Is there
anyway
I can work around this so that I can replace my text fields en masse
with
numerals?

Thanks