Remove Specific Text/Character in Specific Place

  • Thread starter Thread starter Gary Dolliver
  • Start date Start date
G

Gary Dolliver

Hi all,
I have two questions:
1. Is it possible to have text in a specific area be removed from a field?
I have a field [Author] that when imported brings in "By: " before the
author's name. I cannot change the import and thus have to do it on the back
end. It will always be at the beginning of the field, yet, sometimes it may
not be there, so I can remove the first 4 characters on every record - only
the ones that have "By: ".
2. Is it possible to have an ASCII character removed from certain fields?
It only shows up at the end of a field and I believe it is the symbol for a
carriage return (small rectangle) - how would I be able to remove this when
it appears?
Help is greatly appreciated, thanks
-gary
 
STANDARD WARNING: Make a backup of any tables that you update first or even
the entire database.

1: Change A to the actual table name:

UPDATE A
SET A.Author = Mid([Author],5)
WHERE A.Author Like "By: *" ;

2. I don't know if this will work, but try the following with the proper
table and field names:

UPDATE A
SET A.Author = Trim([Author]) ;
 
Hi all,
I have two questions:
1. Is it possible to have text in a specific area be removed from a field?
I have a field [Author] that when imported brings in "By: " before the
author's name. I cannot change the import and thus have to do it on the back
end. It will always be at the beginning of the field, yet, sometimes it may
not be there, so I can remove the first 4 characters on every record - only
the ones that have "By: ".
2. Is it possible to have an ASCII character removed from certain fields?
It only shows up at the end of a field and I believe it is the symbol for a
carriage return (small rectangle) - how would I be able to remove this when
it appears?
Help is greatly appreciated, thanks
-gary
Click to expand...

There are several ways.

Back up your data first.
Create an Update query.

If the only place in the field that "By: " is present is at the
beginning, you could use:

Update YourTable Set YourTable.[Author] = Replace([Author],"By: ","");

or if it can be elsewhere in the field as well, use:

Update YourTable Set YourTable.[Author] = mid([Author],5) Where
Left([Author],4) = "By: ";

To remove the character at the end (and you think it is a Carriage
Return) then:

Update YourTable Set YourTable.[Author] = Replace(
Replace([Author],chr(13),""),chr(10),"");

Which will remove both the carriage return and line feed characters if
either or both are present.

Change YourTable to whatever the actual table name is.
 
Gary said:
I have two questions:
1. Is it possible to have text in a specific area be removed from a field?
I have a field [Author] that when imported brings in "By: " before the
author's name. I cannot change the import and thus have to do it on the back
end. It will always be at the beginning of the field, yet, sometimes it may
not be there, so I can remove the first 4 characters on every record - only
the ones that have "By: ".
2. Is it possible to have an ASCII character removed from certain fields?
It only shows up at the end of a field and I believe it is the symbol for a
carriage return (small rectangle) - how would I be able to remove this when
it appears?
Click to expand...


You need to do this on a case by case basis.

The By: issue might be done using a calculated field like:

IIf(Left(Author, 4) = "By: ", Mid(Author, 4), Author)

But that will rapidly spin out of control with just a few
things to clean up.

Might be better to create a public function in a standard
module where you can use use VBA to help organize the logic
needed to deal with cleaning up several different things.
 
Hi Gary,

1) For your specific example, you can use an update query like this:

UPDATE MyTable
SET [Author] = Mid([Author], 5)
WHERE [Author] LIKE "By: *"
;

2) You'll need to discover just what the character is before you can
run a query to remove it. If you have one of these values displayed in
a control on a form you can get the Unicode value of the last
character by using something like this in the Immediate pane of the
VBE:

?AscW(Right(Forms("XXX").YYY.Value,1))

Replace XXX with the name of the form and YYY with the name of the
control.

Then you can use an update query like this (replacing NNN with the
character code):

UPDATE MyTable
SET [MyField] = Left([MyField], Len([MyField]) - 1)
WHERE AscW(Right([MyField], 1)) = NNN
;

Hi all,
I have two questions:
1. Is it possible to have text in a specific area be removed from a field?
I have a field [Author] that when imported brings in "By: " before the
author's name. I cannot change the import and thus have to do it on the back
end. It will always be at the beginning of the field, yet, sometimes it may
not be there, so I can remove the first 4 characters on every record - only
the ones that have "By: ".
2. Is it possible to have an ASCII character removed from certain fields?
It only shows up at the end of a field and I believe it is the symbol for a
carriage return (small rectangle) - how would I be able to remove this when
it appears?
Help is greatly appreciated, thanks
-gary
Click to expand...
 
Thank you!

Jerry Whittle said:
STANDARD WARNING: Make a backup of any tables that you update first or even
the entire database.

1: Change A to the actual table name:

UPDATE A
SET A.Author = Mid([Author],5)
WHERE A.Author Like "By: *" ;

2. I don't know if this will work, but try the following with the proper
table and field names:

UPDATE A
SET A.Author = Trim([Author]) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Gary Dolliver said:
Hi all,
I have two questions:
1. Is it possible to have text in a specific area be removed from a field?
I have a field [Author] that when imported brings in "By: " before the
author's name. I cannot change the import and thus have to do it on the back
end. It will always be at the beginning of the field, yet, sometimes it may
not be there, so I can remove the first 4 characters on every record - only
the ones that have "By: ".
2. Is it possible to have an ASCII character removed from certain fields?
It only shows up at the end of a field and I believe it is the symbol for a
carriage return (small rectangle) - how would I be able to remove this when
it appears?
Help is greatly appreciated, thanks
-gary
Click to expand...
Click to expand...
 
Thank you!

John Nurick said:
Hi Gary,

1) For your specific example, you can use an update query like this:

UPDATE MyTable
SET [Author] = Mid([Author], 5)
WHERE [Author] LIKE "By: *"
;

2) You'll need to discover just what the character is before you can
run a query to remove it. If you have one of these values displayed in
a control on a form you can get the Unicode value of the last
character by using something like this in the Immediate pane of the
VBE:

?AscW(Right(Forms("XXX").YYY.Value,1))

Replace XXX with the name of the form and YYY with the name of the
control.

Then you can use an update query like this (replacing NNN with the
character code):

UPDATE MyTable
SET [MyField] = Left([MyField], Len([MyField]) - 1)
WHERE AscW(Right([MyField], 1)) = NNN
;

Hi all,
I have two questions:
1. Is it possible to have text in a specific area be removed from a field?
I have a field [Author] that when imported brings in "By: " before the
author's name. I cannot change the import and thus have to do it on the back
end. It will always be at the beginning of the field, yet, sometimes it may
not be there, so I can remove the first 4 characters on every record - only
the ones that have "By: ".
2. Is it possible to have an ASCII character removed from certain fields?
It only shows up at the end of a field and I believe it is the symbol for a
carriage return (small rectangle) - how would I be able to remove this when
it appears?
Help is greatly appreciated, thanks
-gary
Click to expand...
Click to expand...
 
Thank you!

Marshall Barton said:
Gary said:
I have two questions:
1. Is it possible to have text in a specific area be removed from a field?
I have a field [Author] that when imported brings in "By: " before the
author's name. I cannot change the import and thus have to do it on the back
end. It will always be at the beginning of the field, yet, sometimes it may
not be there, so I can remove the first 4 characters on every record - only
the ones that have "By: ".
2. Is it possible to have an ASCII character removed from certain fields?
It only shows up at the end of a field and I believe it is the symbol for a
carriage return (small rectangle) - how would I be able to remove this when
it appears?
Click to expand...


You need to do this on a case by case basis.

The By: issue might be done using a calculated field like:

IIf(Left(Author, 4) = "By: ", Mid(Author, 4), Author)

But that will rapidly spin out of control with just a few
things to clean up.

Might be better to create a public function in a standard
module where you can use use VBA to help organize the logic
needed to deal with cleaning up several different things.
Click to expand...
 
Back
Top