URGENT - delete characters in a record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Crystal - 13 Mar 2006 20:11 GMT

Hi Everyone,

In my table I have a field named Short_Description and in this field
contains various part numbers...example...6312-2RS(D), 1630-DCG(C),
SSR-10-DD(NX)...

I need to remove the characters in the paranthesis including the
paranthesis. I know there has to be an easier way to do this other than a
"Find and Replace" in the actual table...

Your help is greatly appreciated

Reply to this Message

KARL DEWEY - 13 Mar 2006 20:45 GMT

Use an update query like this --
Left([YourField],InStr([YourField],"(")-1) &
Right([YourField],Len([YourField])-InStr([YourField],")"))

> Hi Everyone,
>
[quoted text clipped - 7 lines]
>
> Your help is greatly appreciated

Reply to this Message

Crystal - 13 Mar 2006 21:10 GMT

Karl,
You will have to excuse me but I'm still new at this...This is how I have my
query set up...

Field: Short_Descr
Table: COPYQA
Update to: Left([ALTITEM1],InStr([ALTITEM1],"(")-1) &
Right([ALTITEM1],Len([ALTITEM1])-InStr([ALTITEM1],")"))
Criteria: Like '*(?)*' Or Like '*(??)*'

It seem to be running, but it's actually deleting the entire record instead
of the (?). Please advise if you could.

Once again Thanks

> Use an update query like this --
> Left([YourField],InStr([YourField],"(")-1) &
[quoted text clipped - 11 lines]
> >
> > Your help is greatly appreciated

Reply to this Message

KARL DEWEY - 13 Mar 2006 21:55 GMT

Do not see how it can delete the record.
Create a query in design view and add a field in the output like this --
Changed text: Left([ALTITEM1],InStr([ALTITEM1],"(")-1) &
Right([ALTITEM1],Len([ALTITEM1])-InStr([ALTITEM1],")"))

No criteria on this field. You can put criteria on the orignal field like --
Like "*(*" And Like "*)*"

If pulls the correct records then change the query to an update query and run.

> Karl,
> You will have to excuse me but I'm still new at this...This is how I have my
[quoted text clipped - 26 lines]
> > >
> > > Your help is greatly appreciated

Reply to this Message

Crystal - 13 Mar 2006 22:15 GMT

Karl,
I appreciate your patience with me...one more question...Your code works as
per your instructions however it's deleting everything that's in ( ). I
apologize for not being more specific in the beginning, but I only need to
get rid of the ( ) where it has only 1 or 2 characters in it...example...(D),
(C), (NX), (LX), etc....I need to keep the ones that appear
as...example...(90040), (SC1), (W/O SNAP RINGS), etc...

Thanks

> Do not see how it can delete the record.
> Create a query in design view and add a field in the output like this --
[quoted text clipped - 36 lines]
> > > >
> > > > Your help is greatly appreciated

Reply to this Message

KARL DEWEY - 14 Mar 2006 01:09 GMT

Create another output fields like --
InStr([ALTITEM1],")")-InStr([ALTITEM1],"(")
set criteria for this field as 2 Or 3

> Karl,
> I appreciate your patience with me...one more question...Your code works as
[quoted text clipped - 46 lines]
> > > > >
> > > > > Your help is greatly appreciated

Reply to this Message