[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