[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
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com