Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: Issue with nested replace function

    Thank you in advance for your help, access gurus. I'm a complete beginner so please bear with me if I'm making an obvious mistake. Within a form, I'm trying to set up an automaticly generated hyperlink in one column (named "Drawing") using data from another column (named "Panel").

    The "Panel" column is populated by entries which look like this:

    ZB-E10-P522
    ZB-E10-P524
    ZA-E13T-P01
    etc, etc.

    I'd like the "Drawing" column to contain corresponding entries that look like this:
    \\serverb\10050\ZB\E10\PDF\ZB-E10-P522.pdf

    I've gotten as far as creating this expression:
    Expr1: "\\serverb\10050\" & Replace(Replace([Panel],"-P*",""),"-","\") & "\PDF\" & [Panel] & ".pdf"
    (I'm assuming that it's telling Access to:
    add "\\serverb\10050\" to the front of a string made up of the "Panel" data, but first, replace all characters starting from "-P" on with nothing (""), then, replace all "-" characters with "\" characters. Then, I'm adding "\PDF\" to the end of it, and adding the data from the "Panel" collumn again to the end and finally adding ".pdf" to the end of all that.)

    Which actually results in:
    \\serverb\10050\ZA\E10\P522\PDF\ZB-E10-P522.pdf

    It seems to be ignoring my intended replacemnt of the "-P*" string with "nothing". What am I doing wrong?

    Thanks again!

  2. #2
    Join Date
    Mar 2010
    Posts
    5

    Please help!

    Sorry, I'm really struggling with this one, so I'm bumping the post. Anyone have any ideas?

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'd use InStr() to find the locations of the dashes and split it that way. Or, me being me, I'd more likely pass it to a vba function so I could avail myself of the Split() function.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Replace does not handle wilcards:
    Code:
        Replace([Panel], "-P*", "")
    means "find every substring composed of a minus sign, an uppercase p and an asterisk in [Panel] and replace it by a zero-length string".

    You can easily verify:
    Code:
        Panel = "ZB-E10-P522"
        Debug.Print = Replace(Panel, "-P*", "")
    yields: ZB-E10-P522
    Have a nice day!

  5. #5
    Join Date
    Mar 2010
    Posts
    5
    Thanks for the responses Teddy and Sinndho! I'm currently not able to give the InStr() function a try, but I'll try it first thing in the morn back at work!

    Thanks for the troubleshooting Sinndho! That debug thing is pretty handy.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Though there is no regular expression handler in VBA, it's easy to use one by adding a reference to the Microsoft VBScript Regular Expression to your project.

    From there you can build a RegExReplace function such as:
    Code:
    Function RegExReplace(ByVal AnyString As String, ByVal RegularExpression As String, ByVal ReplacementString As String) As String
    
        Dim RegEx As RegExp
        Dim reMatches As MatchCollection
        Dim reMatch As Match
        Dim str As String
        
        Set RegEx = New RegExp
        RegEx.IgnoreCase = True
        RegEx.Global = True
        RegEx.Pattern = RegularExpression
        Set reMatches = RegEx.Execute(AnyString)
        str = AnyString
        For Each reMatch In reMatches
          str = Replace(str, (reMatch.Value), ReplacementString)
        Next
        Set RegEx = Nothing
        RegExReplace = str
    
    End Function
    Now, RegExReplace("ZB-E10-P522", "\-P.*", "") yields "ZB-E10"

    If you're interested in regular expression, visit:
    Regular-Expressions.info - Regex Tutorial, Examples and Reference - Regexp Patterns
    from where the original idea comes, thanks to the author(s)!
    Attached Thumbnails Attached Thumbnails regex reference.jpg  
    Have a nice day!

  7. #7
    Join Date
    Mar 2010
    Posts
    5
    Thanks Sinndho! You're a life-saver! Unfortunately though, I'm apparently even more of a noob than I had hoped. I'm having trouble integrating your code into my DB. Here are my issues:

    I'm running Access 07 and I can't seem to find the "References" dialogue where I can enable "Microsoft VBscript Regular Expressions". Is it automatically enabled with 07?

    Once that's taken care of, where would I insert that handy "RegExReplace" function that you've made?

    If, with my rudimentary grasp of VBA, I understand what the function is doing, it's basically enabling the use of expressions anywhere within my Access Database? How about for a user who might be "accessing" the DB via a front-end on their own box? Would they also need to enable the "Microsoft VBscript Regular Expressions", or would it be embedded in the DB front end (which I've created and forwarded to them)?

    Thanks for all your help and patience!

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by Sinndho View Post
    Though there is no regular expression handler in VBA, it's easy to use one by adding a reference to the Microsoft VBScript Regular Expression to your project.

    From there you can build a RegExReplace function such as:
    Code:
    Function RegExReplace(ByVal AnyString As String, ByVal RegularExpression As String, ByVal ReplacementString As String) As String
    
        Dim RegEx As RegExp
        Dim reMatches As MatchCollection
        Dim reMatch As Match
        Dim str As String
        
        Set RegEx = New RegExp
        RegEx.IgnoreCase = True
        RegEx.Global = True
        RegEx.Pattern = RegularExpression
        Set reMatches = RegEx.Execute(AnyString)
        str = AnyString
        For Each reMatch In reMatches
          str = Replace(str, (reMatch.Value), ReplacementString)
        Next
        Set RegEx = Nothing
        RegExReplace = str
    
    End Function
    Now, RegExReplace("ZB-E10-P522", "\-P.*", "") yields "ZB-E10"

    If you're interested in regular expression, visit:
    Regular-Expressions.info - Regex Tutorial, Examples and Reference - Regexp Patterns
    from where the original idea comes, thanks to the author(s)!
    Sinndho - If you can create an mdb example of this, I think it would make a great post for the code bank.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In Access 2007 open a new module, paste the code I provided in it, then select References in the Tools menu and check the reference to the library in the dialog box (see the picture I joined to my initial answer).

    This allows you to create instances of the Regexp class in your application but that does not mean that regular expressions are automatically enabled everywhere in your project. You still have to write code to use them, as I did with the RegExReplace function.
    Have a nice day!

  10. #10
    Join Date
    Mar 2010
    Posts
    5
    Thanks Sinndho! I really appreciate it! Can't wait to try it out on Monday!

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •