David McAfee
davidmcafee at gmail.com
Tue Dec 15 12:20:41 CST 2009
I think Pedro mentioned that he had some fields with more than one set
of parenthesis, so they would have to filtered out as the function
concatenates the results (unless that is what he is looking for).
Concl Expr1
abc(gta)xyz(gta2)lmo gtagta2
On Tue, Dec 15, 2009 at 9:42 AM, A.D. Tejpal <adtp at airtelmail.in> wrote:
> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>