stripping text

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

whats the best way to strip part of a string ?
Say I have a report that lists the following
"4 inch nails (round) wire"
"3 inch nails (oval) galv"
I need to be able to stip the "(round)" & the "(oval)"
i.e everything in brackets "(*)"
using replace in the query behind the report I can get it to find the ( or
th ) but not both and not with any text in between
I cannot change the record in the tables as they are link from elswhere
any help would be great

Mike
 
Mike,

This will look messy but try using the Left and Right
functions with the Find and Len function.

=left([somefield],find("(",[somefield]-1))& right
([somefield],len([somefield])-find(")",[somefield]))

This will (hopefully) get the data to the left of the open
bracket and join the data to the right of the close
bracket. You may need to add or subtract 1 to the Find
function if you find the result is one character out.

This was off the top of my head so check my syntax with
Help if you have problems.

HTH,

Terry
 
whats the best way to strip part of a string ?
Say I have a report that lists the following
"4 inch nails (round) wire"
"3 inch nails (oval) galv"
I need to be able to stip the "(round)" & the "(oval)"
i.e everything in brackets "(*)"
using replace in the query behind the report I can get it to find the ( or
th ) but not both and not with any text in between
I cannot change the record in the tables as they are link from elswhere
any help would be great

Mike
Click to expand...

In an unbound control on the Report:
=Left([FieldName],InStr([FieldName],"(")-1) &
Mid([FieldName],InStr([FieldName],")")+1)
 
Mike,

Sorry about the wrong reply earlier. Use Freds response
the Find function is an Excel function NOT an Access
function. Sorry

Terry
-----Original Message-----
whats the best way to strip part of a string ?
Say I have a report that lists the following
"4 inch nails (round) wire"
"3 inch nails (oval) galv"
I need to be able to stip the "(round)" & the "(oval)"
i.e everything in brackets "(*)"
using replace in the query behind the report I can get it to find the ( or
th ) but not both and not with any text in between
I cannot change the record in the tables as they are link from elswhere
any help would be great

Mike
Click to expand...

In an unbound control on the Report:
=Left([FieldName],InStr([FieldName],"(")-1) &
Mid([FieldName],InStr([FieldName],")")+1)
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Thanks for the help but if I use fredg's reply I get errors when there are
no (*) in the field

sorry I never said that not all the fields in the report have the "(*)" in

Mike
Terry said:
Mike,

Sorry about the wrong reply earlier. Use Freds response
the Find function is an Excel function NOT an Access
function. Sorry

Terry
-----Original Message-----
whats the best way to strip part of a string ?
Say I have a report that lists the following
"4 inch nails (round) wire"
"3 inch nails (oval) galv"
I need to be able to stip the "(round)" & the "(oval)"
i.e everything in brackets "(*)"
using replace in the query behind the report I can get it to find the ( or
th ) but not both and not with any text in between
I cannot change the record in the tables as they are link from elswhere
any help would be great

Mike
Click to expand...

In an unbound control on the Report:
=Left([FieldName],InStr([FieldName],"(")-1) &
Mid([FieldName],InStr([FieldName],")")+1)
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
Click to expand...
 
Thanks for the help but if I use fredg's reply I get errors when there are
no (*) in the field

sorry I never said that not all the fields in the report have the "(*)" in
Click to expand...

* snipped *

Tsk! Tsk!
Thirty lashes with a cooked spaghetti noodle.... <g>

You haven't said how you wish to handle the fields that do not have
the (*) text within the field.
Do you wish to print them as is? If so....

=IIf(InStr([FieldName],"(")>0,Left([FieldName],InStr([FieldName],"(")-1)
& Mid([FieldName],InStr([FieldName],")")+1),[FieldName])
 
Thats works great
thanks for the help

Mike

fredg said:
Thanks for the help but if I use fredg's reply I get errors when there are
no (*) in the field

sorry I never said that not all the fields in the report have the "(*)"
Click to expand...
in

* snipped *

Tsk! Tsk!
Thirty lashes with a cooked spaghetti noodle.... <g>

You haven't said how you wish to handle the fields that do not have
the (*) text within the field.
Do you wish to print them as is? If so....

=IIf(InStr([FieldName],"(")>0,Left([FieldName],InStr([FieldName],"(")-1)
& Mid([FieldName],InStr([FieldName],")")+1),[FieldName])
Click to expand...
 
Back
Top