Halting a Repeating Macro on Not Found

[Kathy Webster]

I have a repeating macro that searches for a carat, then deletes it, and
puts a hard return in its place. I am using this as my solution for pasting
data into an Access memo field from word processing tables that had hard
returns, which Access doesn't like. Since I can't replace a carat with a
hard return through the Find and Replace menu, I have devised this macro.
So I replace the hard returns in the Word table with carats, copy and paste
into Access, then find the carats and change them back to hard returns.

When the macro finds no more carats, I can't figure out how to make it stop.
I have read that the REPEAT EXPRESSION argument is designed for this, but I
can't find any examples, and I am unsuccessful at guessing.

I have in the "FIXIT" macro:
Action: RunMacro
Macro name: Find carat and replace
Repeat count: 20
Repeat Expression: Quit()

What happens when I run FIXIT is, it just quits immediately, before it even
finds the first carat.

Can anyone help?
Thanks very much,
Kathy

 

[Steve Schapel]

Kathy,

I assume we are not talking gold purity here, and that you mean caret
(^) character? Your Repeat Expression argument setting would need to
identify when there are no further carets in the text. That's how the
Repeat Expression works... the macro stops when the Repeat Expression is
no longer true. Just guessing here, without seeing what you are doing,
but maybe this will do it...
InStr([YourField],"^")>0

Having said that, there is probably an easier way of doing this, using
the Replace() function in an Update Query.
Update [YourField] to Replace([YourField],"^",Chr(13) & Chr(10))

 

[Kathy Webster]

Steve,
Actually I meant carrot, Doc
Thanks, Steve. Forgive my inexperience in this area, but I have never gotten
this advanced in my update queries.
I tried putting Replace([DescriptionField],"^",Chr(13) & Chr(10)) in the
UPDATE TO line in the query grid in the DescriptionField column, and it
didn't update any records due to a type conversion failure. You must be
laughing now. Helllp!
Kathy

Kathy,

I assume we are not talking gold purity here, and that you mean caret (^)
character? Your Repeat Expression argument setting would need to identify
when there are no further carets in the text. That's how the Repeat
Expression works... the macro stops when the Repeat Expression is no
longer true. Just guessing here, without seeing what you are doing, but
maybe this will do it...
InStr([YourField],"^")>0

Having said that, there is probably an easier way of doing this, using the
Replace() function in an Update Query.
Update [YourField] to Replace([YourField],"^",Chr(13) & Chr(10))

--
Steve Schapel, Microsoft Access MVP

I have a repeating macro that searches for a carat, then deletes it, and
puts a hard return in its place. I am using this as my solution for
pasting data into an Access memo field from word processing tables that
had hard returns, which Access doesn't like. Since I can't replace a
carat with a hard return through the Find and Replace menu, I have
devised this macro. So I replace the hard returns in the Word table with
carats, copy and paste into Access, then find the carats and change them
back to hard returns.

When the macro finds no more carats, I can't figure out how to make it
stop. I have read that the REPEAT EXPRESSION argument is designed for
this, but I can't find any examples, and I am unsuccessful at guessing.

I have in the "FIXIT" macro:
Action: RunMacro
Macro name: Find carat and replace
Repeat count: 20
Repeat Expression: Quit()

What happens when I run FIXIT is, it just quits immediately, before it
even finds the first carat.

Can anyone help?
Thanks very much,
Kathy

 

[Kathy Webster]

I still need help. Can anyone help?
Thank you!
Kathy

Steve,
Actually I meant carrot, Doc
Thanks, Steve. Forgive my inexperience in this area, but I have never
gotten this advanced in my update queries.
I tried putting Replace([DescriptionField],"^",Chr(13) & Chr(10)) in the
UPDATE TO line in the query grid in the DescriptionField column, and it
didn't update any records due to a type conversion failure. You must be
laughing now. Helllp!
Kathy

Kathy,

I assume we are not talking gold purity here, and that you mean caret (^)
character? Your Repeat Expression argument setting would need to
identify when there are no further carets in the text. That's how the
Repeat Expression works... the macro stops when the Repeat Expression is
no longer true. Just guessing here, without seeing what you are doing,
but maybe this will do it...
InStr([YourField],"^")>0

Having said that, there is probably an easier way of doing this, using
the Replace() function in an Update Query.
Update [YourField] to Replace([YourField],"^",Chr(13) & Chr(10))

--
Steve Schapel, Microsoft Access MVP

I have a repeating macro that searches for a carat, then deletes it, and
puts a hard return in its place. I am using this as my solution for
pasting data into an Access memo field from word processing tables that
had hard returns, which Access doesn't like. Since I can't replace a
carat with a hard return through the Find and Replace menu, I have
devised this macro. So I replace the hard returns in the Word table with
carats, copy and paste into Access, then find the carats and change them
back to hard returns.

When the macro finds no more carats, I can't figure out how to make it
stop. I have read that the REPEAT EXPRESSION argument is designed for
this, but I can't find any examples, and I am unsuccessful at guessing.

I have in the "FIXIT" macro:
Action: RunMacro
Macro name: Find carat and replace
Repeat count: 20
Repeat Expression: Quit()

What happens when I run FIXIT is, it just quits immediately, before it
even finds the first carat.

Can anyone help?
Thanks very much,
Kathy

 

[Steve Schapel]

Kathy,

I have tried this exactly as you stated, both with DescriptionField as
Text data type, and also as Memo data type, and it worked fine for me.
I'm afraid I'm stumped as to what is causing the error. Can you give an
example of the data you are working with, and the details of the field
data type. Were you trying to update any other fields at the same time?

 

[Kathy Webster]

Hi Steve,

What I would love help with is your alternate solution of the REPLACE QUERY.
That is what I got excited about in your reply, but I was unable to make it
work. Can you help me with that? I've never done one of them, but it sounds
fantastic!

No, I am not trying to update any other fields at the same time.
The field is named DESCRIPTION, the data type is MEMO. The data in it looks
like this:

A line of text^a second line of text^a third line of text^^a fourth line of
text.

There may be one or more carets within the text OR NONE. That part is
inconsistent.

Therefore, I feel this repeating macro solution is not the way to go,
especially since:
1. it dies if there are NO carets, and
2. it has to be executed repeatedly because of the 20 repetitions
limitation of the repeating macro.

Thank you,
Kathy

 

[Steve Schapel]

Hi Kathy,

It was the Update Query using the Replace() function that I was
referring to. As I said, I tried it exactly as the syntax of your
earlier message. I can't understand where the type conversion failure
is coming into it. What version of Access are you using? Just as an
experiment, on a backup copy, can you try replacing the ^ with a
standard character rather than the carriage return, for example put this
in the Update To row of the query...
Replace([DESCRIPTION],"^","x")
Also, can you please select SQL from the View menu in design view of the
query, and copy/paste the SQL of the query into your reply. Thanks.

 

[Kathy Webster]

Hi Steve,
Yummy, its a lovely thing, and it works great! Thanks for all your help,
and for teaching me a new technique...the replace function in a query!
Kathy

Hi Kathy,

It was the Update Query using the Replace() function that I was referring
to. As I said, I tried it exactly as the syntax of your earlier message.
I can't understand where the type conversion failure is coming into it.
What version of Access are you using? Just as an experiment, on a backup
copy, can you try replacing the ^ with a standard character rather than
the carriage return, for example put this in the Update To row of the
query...
Replace([DESCRIPTION],"^","x")
Also, can you please select SQL from the View menu in design view of the
query, and copy/paste the SQL of the query into your reply. Thanks.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

What I would love help with is your alternate solution of the REPLACE
QUERY. That is what I got excited about in your reply, but I was unable
to make it work. Can you help me with that? I've never done one of them,
but it sounds fantastic!

No, I am not trying to update any other fields at the same time.
The field is named DESCRIPTION, the data type is MEMO. The data in it
looks like this:

A line of text^a second line of text^a third line of text^^a fourth line
of text.

There may be one or more carets within the text OR NONE. That part is
inconsistent.

Therefore, I feel this repeating macro solution is not the way to go,
especially since:
1. it dies if there are NO carets, and
2. it has to be executed repeatedly because of the 20 repetitions
limitation of the repeating macro.

Thank you,
Kathy

 

[Steve Schapel]

Kathy,

Yummy? Looks like we've moved from carats to carets to carrots now.

Anyway, I'm a bit confused here. I thought we already looked at this
darn Update Query with the Replace() function before, but you got an
error? Anyway, working now, apparently, so that's yummy indeed.