[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
>