A.D. Tejpal
adtp at airtelmail.in
Tue Dec 15 11:42:02 CST 2009
Pedro,
For extracting & merging contents of multiple sets of ( ) occurring in source string, you could use following update query:
Sample update query:
======================================
UPDATE MyTable SET fldSelection = Fn_GetDataInBrackets([Concl]);
======================================
Fn_GetDataInBrackets() as given below, is a user defined function.
Best wishes,
A.D. Tejpal
------------
' Code in general module
'======================================
Function Fn_GetDataInBrackets( _
InputString As String) As String
Dim Rtv As Variant, Cnt As Long
Dim Txt As String, SubTxt As String
Dim Pos As Long
Txt = ""
Rtv = Split(InputString, "(")
If UBound(Rtv) > 0 Then
For Cnt = 0 To UBound(Rtv)
SubTxt = Rtv(Cnt)
Pos = InStr(SubTxt, ")")
If Pos > 1 Then
Txt = Txt & Left(SubTxt, Pos - 1)
End If
Next
End If
Fn_GetDataInBrackets = Txt
End Function
'======================
----- Original Message -----
From: Pedro Janssen
To: Access Developers discussion and problem solving
Sent: Tuesday, December 15, 2009 02:59
Subject: Re: [AccessD] text between ( )
Hello David,
thanks again.
Pedro
----- Original Message -----
From: "David McAfee" <davidmcafee at gmail.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Monday, December 14, 2009 8:06 PM
Subject: Re: [AccessD] text between ( )
Just off the top of my head, I would run a second query to catch those.
The Like statement would have to be like this:
Like "*(*)*(*)*"
This select statement should get you going in the right direction,
look at the "Solution2" expression:
SELECT YourTable.Concl,
Mid([Concl],InStr(1,[Concl],"(")+1,(InStr(InStr(1,[Concl],"("),[Concl],")"))-1-(InStr(1,[Concl],"(")))
AS Solution1, InStr(1,[Concl],"(") AS firstOpenPar,
InStr(InStr(1,[Concl],"("),[Concl],")") AS firstClosepar,
InStr([firstOpenPar]+1,[Concl],"(") AS 2ndOpenPar,
InStr(InStr([2ndOpenPar],[Concl],"("),[Concl],")") AS 2ndClosepar,
Mid([Concl],[2ndOpenPar]+1,[2ndClosepar]-1-[2ndOpenPar]) AS Solution2
FROM YourTable
WHERE (((YourTable.Concl) Like "*(*)*(*)*"));
On Mon, Dec 14, 2009 at 10:23 AM, Pedro Janssen <pedro at plex.nl> wrote:
> Hello david,
>
> thanks for your explanation.
>
> You say:
> "A double situation will only result in the first set showing up:
> Concl Expr1
> abc(gta)xyz(gta2)lmo gta"
>
> How can i get this result for "A double stuation"
> Concl Expr1
> abc(gta)xyz(gta2)lmo gta & gta2
>
> Pedro
>
>
> ----- Original Message -----
> From: "David McAfee" <davidmcafee at gmail.com>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Monday, December 14, 2009 7:07 PM
> Subject: Re: [AccessD] text between ( )
>
> Sorry about that. The Mid statement should read as follows:
>
> Mid([Concl],InStr(1,[Concl],"(")+1,(InStr(1,[Concl],")"))-1-(InStr(1,[Concl],"(")))
>
> That gives me:
> PhoneNo Expr1
> (818)5551212 818
> (626)555-1212 626
> (626)5554687 626
> aaa (g2Ta)bbbbcccc g2Ta
> aaaaa(g2Ta)cccccccdddddd g2Ta
>
> This gives us the 1st (open) parenthesis:
> InStr(1,[Concl],"(")
>
> This gives us the 1st closed parenthesis
> InStr(1,[Concl],")")
>
>
> This is the length of everything between the two:
> (InStr(1,[Concl],")"))-1-(InStr(1,[Concl],"("))
>
> If you have an closed parenthesis before the first open one, the
> statement will give you an error:
> Concl Expr1
> 111)sdsf(ssgs)gsgs #Error
>
> If we replace the "1" in the closed statement, with the 1st open
> statement, we can correct this error.
> So instead of starting at position 1, we will start after the first
> found open parenthesis
> Mid([Concl],InStr(1,[Concl],"(")+1,(InStr(InStr(1,[Concl],"("),[Concl],")"))-1-(InStr(1,[Concl],"(")))
>
> Concl Expr1
> 111)sdsf(ssgs)gsgs ssgs
>
> A double situation will only result in the first set showing up:
> Concl Expr1
> abc(gta)xyz(gta2)lmo gta
>
> Anything like this will be filtered out with the Like statement:
> Concl Expr1
> lalala)djjdjsdgs #Error
> dfsdsgsg #Error
> (sgsgsgssgs #Error
>
> HTH,
> David McAfee
>
> On Mon, Dec 14, 2009 at 1:33 AM, Pedro Janssen <pedro at plex.nl> wrote:
>> Hello David,
>>
>> the query, almost does what i want.
>>
>> SELECT Concl,
>> Mid([Concl],InStr(1,[Concl],"(")+1,InStr(InStr(1,[Concl],"("),[Concl],")")-2)
>> AS Expr1
>> FROM YourTable
>> WHERE (((Concl) Like "*(*)*"));
>>
>> - Only when there are more values with ( ) it only returns the first
>> value
>> - The value that is returned ( ) also gives part of the rest of the concl
>> text after the ) but sometimes it is cut off after a certain length and
>> sometimes not. I Tried to variabel the "compare argument", but no result.
>>
>> for example (what the expression returns)
>>
>> Concl expr1
>> aaa (g2Ta)bbbbcccc g2Ta)bbbbc
>> aaaaa(g2Ta)cccccccdddddd g2Ta)ccccccccdddd
>>
>> i don't see the logic of the lengt of the text part after the ) in expr1
>>
>> How can i solve this?
>>
>> Pedro
>>
>> ----- Original Message -----
>> From: "David McAfee" <davidmcafee at gmail.com>
>> To: "Access Developers discussion and problem solving"
>> <accessd at databaseadvisors.com>
>> Sent: Wednesday, December 09, 2009 6:58 PM
>> Subject: Re: [AccessD] text between ( )
>>
>> If this looks good:
>> SELECT Concl,
>> Mid([Concl],InStr(1,[Concl],"(")+1,InStr(InStr(1,[Concl],"("),[Concl],")")-2)
>> AS Expr1
>> FROM YourTable
>> WHERE (((Concl) Like "*(*)*"));
>>
>> you can do it like this:
>> UPDATE YourTable SET YourTable.fldSelection =
>> Mid([Concl],InStr(1,[Concl],"(")+1,InStr(InStr(1,[Concl],"("),[Concl],")")-2)
>> WHERE (((YourTable.Concl) Like "*(*)*"));
>>
>> On Wed, Dec 9, 2009 at 8:13 AM, Pedro Janssen <pedro at plex.nl> wrote:
>>> Dear Group,
>>>
>>> is it possible to filter out text between ( ) in a field [Concl] and
>>> place only the text between ( ) in a new field [fldSelection], with a query.
>>>
>>> Thanks
>>>
>>> Pedro