[AccessD] text between ( )

Thanks Jack and Pat,

this is very usefull.

Pedro



----- Original Message ----- 
From: "Jack and Pat" <drawbridgej at sympatico.ca>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Monday, December 14, 2009 3:37 PM
Subject: Re: [AccessD] text between ( )


> Pedro,
>
> Pedro,
>
> Its early and no one is answering.
>
> Here's a function that will remove  all braces/brackets from a string.
> Watch for line wrap.
>
> '---------------------------------------------------------------------------
> ------------
> ' Procedure : fExtractBraces
> ' Author    : Jack
> ' Created   : 12/14/2009
> ' Purpose   : To remove "(" and ")" from a string
> '---------------------------------------------------------------------------
> ------------
> ' Last Modified:
> '
> ' Inputs: String that may contain any number of "(" or ")"
> ' Dependency: N/A
> '---------------------------------------------------------------------------
> ---
> '
> Function fExtractBraces(strInString As String) As String
> Dim lngLen As Long, strOut As String
> Dim i As Long, strTmp As String
>   On Error GoTo fExtractBraces_Error
>    lngLen = Len(strInString)
>    strOut = ""
>    For i = 1 To lngLen
>        strTmp = Left$(strInString, 1)
>        strInString = Right$(strInString, lngLen - i)
>        If (Asc(strTmp) <> 40 And Asc(strTmp) <> 41) Then
>             strOut = strOut & strTmp
>        End If
>    Next i
>    fExtractBraces = strOut
>   On Error GoTo 0
>   Exit Function
> fExtractBraces_Error:
>    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure
> fExtractBraces"
> End Function
>
> Here is a test procedure
>
> Sub Pedro()
> Dim x As String
> x = "abcd(rst)xyz(abf)"
> Debug.Print x
> Debug.Print fExtractBraces(x)
> End Sub
>
>
> With result:
> abcd(rst)xyz(abf)
> abcdrstxyzabf
>
>
>
> I've also tried it in SQL query on one of my tables:
>
> Sub Pedro1()
> Dim sql As String
> Dim x As String
> x = "abcd(rst)xyz(abf)"
> Debug.Print x
> Debug.Print fExtractBraces(x)
> '
> 'test  using sql query
>
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Set db = CurrentDb
> Set rs = db.OpenRecordset("Select petname,fextractbraces(petname) from 
> pet")
> Do While Not rs.EOF
> Debug.Print rs!petname & "..." & fExtractBraces(rs!petname)
> rs.MoveNext
> Loop
> '
> '  Update the petname
> Dim UpdateText As String
> UpdateText = "Update pet Set petname = fExtractBraces(petname) " _
> & " where petname like '*(*' "
> db.Execute UpdateText, dbFailOnError
>
> End Sub
>
>
> With results:
> Fluffy...Fluffy
> Puffy...Puffy
> accde(ew)43wj(d)...accdeew43wjd
>
> Then after executing the Update statement
>
> Pet_Id PetName     PetOwner
> 003 Fluffy                   O'Keefe
> 006 Puffy                   O'Keefe
> 009 accdeew43wjd     Jim
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
> Sent: Monday, December 14, 2009 4:33 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] text between ( )
>
> 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
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.716 / Virus Database: 270.14.105/2562 - Release Date: 
> 12/14/09
> 02:37:00
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>