Removing text from the middle of a string - in Access

[NeoPa]

goodaan:
Yes this is the sort of answer I am looking for. However I am using the expression Builder in a query, and I have a field name instead of strA:

I'm sorry if you're just looking for an answer to be done for you. As you'll see from my previous post, that's not really what we're here to offer you. Certainly we'll give examples where appropriate, and sometimes they may even suit your precise requirements.

However, if what you have doesn't help you enough to understand how to proceed then please explain clearly what you currently don't understand. As of now it's not clear to me from what you've posted whether you still have a problem, and if so where. There have been a number of examples posted which look to me like they would be enough to help you do this. Some of which you seem to have ignored completely.

If there's one that you'd prefer to work with but need further clarification on then please post clearly what that is and what help you need.

One tip I can give that may help is that when working within a query and specifying a field using any functions, if you enter them all in lower case then all valid functions will be automatically fixed to the same case as is required for that function. Any that remain in lower case will be unrecognised by the SQL engine processing them and may need to be looked at. For that reason it's very important when posting what you are currently working with that you copy it directly from your work after it's been entered into your query. That gives us clues missing from what you've posted for us in post #13 for instance.

[hvsummer]

@NeoPa: yes sir, your respond is somewhat explain everythings.
next time I'll give indirect code (as an example) and explain rather than an direct answer.

btw, I suggest that if you guy post anything to help them, you guy should explain more detail about the method that we suggest them to follow, classify by SQL/query and VBA code to let them know where to apply/try those code.

because I saw a lot of people seem don't know what happen and can't explain their current situation in technically languages. And an example like this:

RegExReplace([FieldName],'^\([^(]*\)([^)]*)\([^(]*\)$','')

will make them go deeper into a mist. (even me, did a lot of homework still don't know what that '^\([^(]*\)([^)]*)\([^(]*\)$','' represent for...)

I know they have to do their homework (search and research/study). but not everyone can easily understand computing science in short time.

[goodaan]

What is this lecture?

I've read all answers. I also gave EXACTLY what I am looking for. You all give a string as an example. I am working with a variable string in a field.

Yes I do understand the theory. No I have not found anything that helps me. Either my syntax is wrong or it's not possible to replace the Str with a field. ZMBD said it was not possible with a field.

If I take this answer:

left(strA,len(strA) - instr(1, strA, "(",0)) _
    & right( strA, len(strA) - instr(1, strA, ")", 0))

and replace strA with my field name, [FIELD], it does not work.

However this was the most helpful answer as it's easier for me to understand and I can see clearly the syntax.

Yes, I can always look it up in the Pears dictionary... so please do not waste any more of your time doing my 'WORK'.

Merry Xmas (I'm working)

p.s. I shall continue removing the text in brackets manually

p.p.s I gave you the code I put in the query expression Builder:

TO_DESCRIPTION: 
   left([FROM_DESCRIPTION],len([FROM_DESCRIPTION])
       - instr(1, [FROM_DESCRIPTION], "(",0)) 
       + right( [FROM_DESCRIPTION], len([FROM_DESCRIPTION]) 
      - instr(1, [FROM_DESCRIPTION], ")", 0))

[zmbd]

Goodan, I understand you are frustrated; however, Please read thru to the end of this post - it's long; however, certainly not intended to be a lecture.

goodaan I've read all answers. I also gave EXACTLY what I am looking for.

+ In your initial post, all you gave were the strings and the desired outcome.
+ You do not indicate in any way shape or form that you desiring a primarily query based solution.
+ You do not indicate which version of Office/Access you are using (however, in this case, the version doesn't matter)
+ You also do not indicate what level of experience you have with Access.
+ You asked a general question and post#2 and Post#4, and Post#5 provided some general approaches to the solution.

+ Really was not until after hvsummer's post (Post#13) that you indicated that you wanted a query based approach.

goodaanYou all give a string as an example. I am working with a variable string in a field.

+ The values in your fields are strings/text; thus, we gave you some options to handle strings. The values in your field would be passed to these functions by reference to the field name. Access understands in a query or record-set that the value in the field for the current record is to be passed to the function(s)

goodaanZMBD said it was not possible with a field.

+1st, I apologize for misleading you. I envisioned a solution based on VBA, namely that you could create a user-defined-function based on one of the options in post#2, and use that either in your select query or as an update query against your data.

I did notice in post#7 and post#8 that you were using a calculated field/control; and I should have more clearly stated in Post#9 that the implementation might be easier in VBA, especially if one is wanting to make a permanent change to the original data as it's a little less fiddly when creating an update-query. Let me clear, this can be directly accomplished via an update-query without any VBA whatsoever... I suggest creating the SELECT query first to make sure that the records that may be affected are properly returned and that the new value is properly formatted, it is then fairly easy to switch this to an update-action-query.

goodaanand replace strA with my field name, [FIELD], it does not work.

+ Telling us only that it doesn't work, doesn't help us to help you. Are you getting an error in the field such as "#Error" or "#Name" or nothing at all?

goodaan

No I have not found anything that helps me. Either my syntax is wrong or it's not possible to replace the Str with a field

p.s. I shall continue removing the text in brackets manually

p.p.s I gave you the code I put in the query expression Builder:

Twice here you indicate that nothing is working for you at this point - most likely it's a syntax error; however, then you post another set of code, did this work for you? If not then I suggest the following:

(Of course, you need to replace the [Test] with your field name. This was the field I used to verify that the following worked:

rmvprnths: Left([Test],(InStr([Test],"(")-1)) & Mid([Test],(InStr([Test],")")+1))

This is the simplest query based implementation of Option two in post#2; however, instead of Right() I've used the Mid() string functions.
To use,
Create a new query
Add your table
Any fields you want shown to the grid
In a blank column in the grid, copy and paste the above in to the "field row"
> This may leave extra spaces in the resulting string as it is cutting exactly on the "(" and ")" characters. Thus something like"
"aaa.-.bbbb.(.NNNN.). CCCC"
Will return "aaa.-.bbbb..CCCC" where the periods represent spaces. Using one of the Trim Functions remove the undesired spaces.
> If you run this without changing the "Test" to your table's field name you will be prompted to enter a "Test" value.
> The query field name will be shown as [rmvprnths] of course you might want to change that to something else?! :)
> This also avoids the regular-expressions, that although are very powerful, I think in this case, may be a bit esoteric for your application.

:)

goodaanso please do not waste any more of your time doing my 'WORK'.

Please take a deep-breath here... you have had the attention of four people here that have tried to help, advise, suggest possible solutions. Each of us with a slightly different approach in both potential solutions and in communication styles. Text based communications have a serious flaw in that we do not get to hear the intonation nor see the "body language" of the other person. Quite simply, in this format, without those queues/clues, it is too easy to take offense where none is intended. Even the silly emoticons don't always provide enough.

goodaanMerry Xmas (I'm working)

\\\Merry Christmas//
(As am I - production labs run 7/24/365)

[NeoPa]

hvsummer:
And an example like this:

RegExReplace([FieldName],'^\([^(]*\)([^)]*)\([^(]*\)$','')

Regular expressions are certainly not easy for people to deal with who are not comfortable with computer science, as you say. That's why, on this particular occasion and although a link had already been posted to another thread which included the required explanations, I included the actual code required. That's the closest I can come to what will actually work for goodaan in the scenario he's outlined. It was theoretical, so I don't guarantee it, but I felt the subject was complicated enough that expecting someone with no relevant experience to work their way through what was there would be a little too much.

hvsummer:
(even me, did a lot of homework still don't know what that '^\([^(]*\)([^)]*)\([^(]*\)$','' represent for...)

Let me try to explain as clearly as I can in case of interest. Goodaan hasn't posted any reply to this yet so I have no idea if they are interested in pursuing it, but as stated earlier, many more people read these threads than just those involved posting in them.

First let me state that it's important to review the code in the linked thread. Without copying that specific code into your database as instructed none of what follows will work or make sense. RegExReplace() takes three parameters :

1. The original string value. This can be a field reference and can also handle a value of Null if passed.
2. The pattern to match against the first parameter. This can contain regular expressions.
3. The pattern to replace the original with. This controls which value is returned by the function. My value suggestion is clearly wrong here. I just noticed this as I was trying to formulate an explanation. It should read '\1\2' instead.

^ - This starts the searching at the beginning of a line. In this case I would expect it to start at the beginning of the value passed.
\( & \) - mark the beginnings and ends of marked groups that can be referenced in the replacement expressions.
[...] - marks a single character expression that is any one of the characters within the brackets.
[^...] - marks a single character expression that is any character other than those within the brackets after the ^. Thus [^(] refers to any character except (.
* - means to repeat the previous expression as many times as it can, which may be none. No repeats still means at least one occurrence of course, to match the original specification.
$ - Marks the end of the value.
, - Parameter separator.
'' - Empty string as replacement string. As mentioned earlier - this is not a correct value. It should be '\1\2'.

Thus :
Start at the beginning; Continue while characters <> '(' & mark string traversed for later as \1 (First such block); Process past '('; Continue while characters <> ')'; Process past ')'; Continue while characters <> '(' & mark string traversed for later as \2 (Second such block).

That last bit should probably not contain anything that cares whether or not it's an '(' and matching the first such '(' is irrelevant. So, my recommendation should actually be :

COM_NEW_DESCRIPTION: RegExReplace([COM_APP_LNK_DESCRIPTION],'^\([^(]*\)[^)]*)\(.*\)$','\1\2')

. - Means one of any character.

NB. I used the field names you used in post #7.

[NeoPa]

@Goodaan.
Please read my PM about your interactions on this site (Private Message Inbox).

[hvsummer]

merry Xmas guys

@goodaan:

if you apply that code into an query that have multi table joined together, you have to add table's name before the field's name

[table1].[Field'sName]

that what I guess you're missing.

btw, you should give some error information as zmbd suggestion to let us know what exactly wrong there ^^

[NeoPa]

hvsummer:
if you apply that code into an query that have multi table joined together, you have to add table's name before the field's name

While that may be true in some cases that is actually quite rare. It is only required when referring to a field name that is the same for more than one of the record sources of a query. Otherwise, Access is clever enough to determine exactly which field you're referring to.

Bear it in mind though, as it can be a problem in such circumstances.

[hvsummer]

Yup, you're right, some case it could be true, some case it was wrong.

to be sure, I think he should add table's name, it can prevent thing go wrong like you guy alway advice to normalize data :D

[goodaan]

Bingo! I could kick myself as the answer is in fact very simply. I am new to Access and do not know the InStr function. I should have thought how to do this in BO or Excel. The Trim works, as well as -2 instead of -1 from the left position.

I suppose I thought everyone uses queries in Access since I am not a programmer - I'll try to be clearer next time. Thank you zmbd for the answer I was looking for. Thanks to all others who joined in.

Like for email, I say nothing if I have nothing to say - this does not mean I was ignoring the answers. I just found most unnecessarily complicated and I did not see how to implement them in the query :-)

[NeoPa]

goodaan:
Like for email, I say nothing if I have nothing to say - this does not mean I was ignoring the answers.

That's fair enough. I would just advise that most experts will perceive that as inappropriate though, for the OP (Original Poster or instigator of the thread). I don't imagine I need to explain why.

I'm very pleased you managed to find your solution anyway.

Merry Christmas.

[NeoPa]

FYI:
I played around with the RegEx code a little, and was certainly frustrated by the paucity of information, particularly appropriate information, in the linked article. Eventually I found the following to work :

COM_NEW_DESCRIPTION: RegExReplace([COM_APP_LNK_DESCRIPTION],'^([^(]*)[^)]*\)(.*)$','$1$2')

NB. This will typically leave a double-space in the middle of the result if the data was in the format :

Before:    XXXXX (YYYYY) ZZZZZ
After:     XXXXX  ZZZZZ

This again uses the code found in Macro to format data in MS Access Form.