|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 13th, 2006, 07:11 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
URGENT - delete characters in a record
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 
|
|
#2
March 13th, 2006, 07:45 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
URGENT - delete characters in a record
Use an update query like this --
Left([YourField],InStr([YourField],"(")-1) & Right([YourField],Len([YourField])-InStr([YourField],")")) "Crystal" wrote: 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
|
|
#3
March 13th, 2006, 08:10 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
URGENT - delete characters in a record
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 "KARL DEWEY" wrote: Use an update query like this -- Left([YourField],InStr([YourField],"(")-1) & Right([YourField],Len([YourField])-InStr([YourField],")")) "Crystal" wrote: 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
|
|
#4
March 13th, 2006, 08:55 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
URGENT - delete characters in a record
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. "Crystal" wrote: 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 "KARL DEWEY" wrote: Use an update query like this -- Left([YourField],InStr([YourField],"(")-1) & Right([YourField],Len([YourField])-InStr([YourField],")")) "Crystal" wrote: 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
|
|
#5
March 13th, 2006, 09:15 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
URGENT - delete characters in a record
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 "KARL DEWEY" wrote: 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. "Crystal" wrote: 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 "KARL DEWEY" wrote: Use an update query like this -- Left([YourField],InStr([YourField],"(")-1) & Right([YourField],Len([YourField])-InStr([YourField],")")) "Crystal" wrote: 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
|
|
#6
March 14th, 2006, 12:09 AM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
URGENT - delete characters in a record
Create another output fields like --
InStr([ALTITEM1],")")-InStr([ALTITEM1],"(") set criteria for this field as 2 Or 3 "Crystal" wrote: 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 "KARL DEWEY" wrote: 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. "Crystal" wrote: 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 "KARL DEWEY" wrote: Use an update query like this -- Left([YourField],InStr([YourField],"(")-1) & Right([YourField],Len([YourField])-InStr([YourField],")")) "Crystal" wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| how do i delete a record without a search key? | BBR | General Discussion | 3 | February 17th, 2006 04:17 PM |
| Problem with delete duplicates query | Tracey | Running & Setting Up Queries | 6 | January 5th, 2006 09:28 PM |
| Adding tables | Gertjan | Running & Setting Up Queries | 1 | December 19th, 2005 04:20 PM |
| Yet another beginner that cannot delete record in a half filled fo | Richard Smith | Using Forms | 1 | March 16th, 2005 02:25 PM |
| Deleting Records does not Delete Records | Lucky Man Cree | New Users | 7 | July 6th, 2004 11:33 PM |
Linear Mode
