Pedro Janssen
pedro at plex.nl
Mon Dec 14 15:28:56 CST 2009
Hello Jack,
doesn't matter.
This was also usefull.
Normaly i use the replace button.
But this function is a better way for me to automate that .
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 10:06 PM
Subject: Re: [AccessD] text between ( ) --Pedro
> Pedro,
> The function I gave was to remove the brackets. I didn't read the details.
> Sorry for any inconvenience.
>
> jack
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pedro Janssen
> Sent: Monday, December 14, 2009 1:20 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [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
>>
>
> --
> 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
>