[AccessD] text between ( )

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