Reports - Find and Replace & (ampersand)

[The Boondock Saint]

Howdy all, Ive got a problem thats totally got me stuck I thought someone
might have come accross it before.

Ive got a report, and some of the data in the report has the ampersand (&)
symbol, which is fine,

However I need to change it slightly so that if there is a ampersand symbol
it will put a carat (^) symbol before the ampersand

so that if the line of text had say

rabbits&sheep&goats
it would change it to
rabbits^&sheep^&goats.

Any ideas on how this could be done.... I need to keep the orginal data as
it is, its just in the report i need to change it.

Cheers and look forward to any thoughts and ideas.

 

[Guest]

Use the table in this instance. Select the whole column where the & is and
from the edit menu, select replace.

In the find box Type &
In the replace box type ^&

Press Find and step through replacing the ones you want or press replace all
to do a block replace.

Oh, dont forget to back up first as always

hth Mike B
--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

 

[Guest]

Sorry, mis read your request, disregard previous advice which will change the
table.

--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

 

[Guest]

Have you tried doing this in a query? Something like this:

SELECT Replace([FieldName],"&","^&") AS Animals
FROM [TableName];

Make the appropriate substitutions in the SQL statement shown above for
FieldName and TableName. Base your report on this query (after adding
additional fields to suite).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[The Boondock Saint]

Thanks for the help Tom

The report is already based on a query...
is there any way I can use a expression in that query or would i have to
build another query? anyway?

I dont have much experience with sql, I normally just fill in the
field,table,show options in a query...

Any advice would be awesome.
Cheers

Have you tried doing this in a query? Something like this:

SELECT Replace([FieldName],"&","^&") AS Animals
FROM [TableName];

Make the appropriate substitutions in the SQL statement shown above for
FieldName and TableName. Base your report on this query (after adding
additional fields to suite).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Howdy all, Ive got a problem thats totally got me stuck I thought someone
might have come accross it before.

Ive got a report, and some of the data in the report has the ampersand (&)
symbol, which is fine,

However I need to change it slightly so that if there is a ampersand symbol
it will put a carat (^) symbol before the ampersand

so that if the line of text had say

rabbits&sheep&goats
it would change it to
rabbits^&sheep^&goats.

Any ideas on how this could be done.... I need to keep the orginal data as
it is, its just in the report i need to change it.

Cheers and look forward to any thoughts and ideas.

 

[Douglas J. Steele]

Yes, you can add the expression Replace([FieldName],"&","^&") AS Animals to
your existing query.

You can also just change the ControlSource of the textbox on your report to
be

=Replace([FieldName],"&","^&")

(including the equal sign)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the help Tom

The report is already based on a query...
is there any way I can use a expression in that query or would i have to
build another query? anyway?

I dont have much experience with sql, I normally just fill in the
field,table,show options in a query...

Any advice would be awesome.
Cheers

Have you tried doing this in a query? Something like this:

SELECT Replace([FieldName],"&","^&") AS Animals
FROM [TableName];

Make the appropriate substitutions in the SQL statement shown above for
FieldName and TableName. Base your report on this query (after adding
additional fields to suite).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Howdy all, Ive got a problem thats totally got me stuck I thought someone
might have come accross it before.

Ive got a report, and some of the data in the report has the ampersand (&)
symbol, which is fine,

However I need to change it slightly so that if there is a ampersand symbol
it will put a carat (^) symbol before the ampersand

so that if the line of text had say

rabbits&sheep&goats
it would change it to
rabbits^&sheep^&goats.

Any ideas on how this could be done.... I need to keep the orginal data as
it is, its just in the report i need to change it.

Cheers and look forward to any thoughts and ideas.

 

[The Boondock Saint]

Thanks very much Douglas,Tom and Mike,
Thats got it working perfectly cheers guys...

Yes, you can add the expression Replace([FieldName],"&","^&") AS Animals to
your existing query.

You can also just change the ControlSource of the textbox on your report to
be

=Replace([FieldName],"&","^&")

(including the equal sign)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the help Tom

The report is already based on a query...
is there any way I can use a expression in that query or would i have to
build another query? anyway?

I dont have much experience with sql, I normally just fill in the
field,table,show options in a query...

Any advice would be awesome.
Cheers

Have you tried doing this in a query? Something like this:

SELECT Replace([FieldName],"&","^&") AS Animals
FROM [TableName];

Make the appropriate substitutions in the SQL statement shown above for
FieldName and TableName. Base your report on this query (after adding
additional fields to suite).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Howdy all, Ive got a problem thats totally got me stuck I thought someone
might have come accross it before.

Ive got a report, and some of the data in the report has the

ampersand
(&)

symbol, which is fine,

However I need to change it slightly so that if there is a ampersand symbol
it will put a carat (^) symbol before the ampersand

so that if the line of text had say

rabbits&sheep&goats
it would change it to
rabbits^&sheep^&goats.

Any ideas on how this could be done.... I need to keep the orginal

data
as

it is, its just in the report i need to change it.

Cheers and look forward to any thoughts and ideas.