Remove Specific Text/Character in Specific Place

[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

 

[Jerry Whittle]

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]) ;

 

[fredg]

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

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.

 

[Marshall Barton]

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?

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.

 

[John Nurick]

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

 

[Gary Dolliver]

Thank you!

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.

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

 

[Gary Dolliver]

Thank you!

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

 

[Gary Dolliver]

Thank you!

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?

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.