Nested REPLACE Functions

[jeran042]

Good morning all,

I have a question. I have a field on my form that picks up the forms filter.

=[Forms]![frm: Report Creator]![frm:Ledger_Detail_Reports_Subform].[Form].[Filter]

Originally, it was not intended to be a visible field, only meant to be a reference in some VBA code.

However now, I believe there is some value in showing the forms criteria, but there is so many characters like [ , ] , ( , or ) that will make this field not user friendly. The best I came up with is a lot of nested REPLACE functions:

=Replace(Replace(Replace(Replace(Replace([Forms]![frm: Report Creator]![frm:Ledger_Detail_Reports_Subform].[Form].[Filter],"[",""),"]",""),"(",""),")",""),"'","")

.

I know this cant be the best way to approach this. Can Anyone with a more elegant approach, please offer some advice?

Much appreciated

 

[June7]

That's what I would use.

 

[Frothingslosh]

If you don't want the nested Replace statements, then you might try something like this:

Private Function MakeAlphaNumeric(ByVal FileNumber As String) As String

Dim x As Long
Dim CleanString As String
Dim CheckedChar As String
Dim AVal As Long

    For x = 1 To Len(FileNumber)
        CheckedChar = Mid(FileNumber, x, 1)
        AVal = Asc(CheckedChar)
        [COLOR=seagreen]'Ensure that only letters and numbers are included[/COLOR]
        If Not ( _
                    (AVal >= 48 And AVal <= 57) _
                    Or (AVal >= 65 And AVal <= 90) _
                    Or (AVal >= 97 And AVal <= 122) _
                    Or AVal = 131 Or AVal = 138 Or AVal = 140 Or AVal = 142 _
                    Or AVal = 154 Or AVal = 156 Or AVal = 158 Or AVal = 159 _
                    Or (AVal >= 192 And AVal <= 246) _
                    Or (AVal >= 248 And AVal <= 255) _
                ) Then
            CheckedChar = ""
        End If
        [COLOR=seagreen]'Add CheckedChar to CleanString[/COLOR]
        CleanString = CleanString & CheckedChar
    Next x
    
    MakeAlphaNumeric = CleanString
    
End Function

This strips out any character that is not a letter or number. It allows assorted letters that don't appear in English - that's why there are so many OR lines.

I use it as part of validation in a couple forms, as it would probably seriously slow down large queries.

Honestly, though, if you're looking for elegance, what you have is probably it. It's just hard to parse, that's all.

 

[CJ_London]

I'm with June

But if you are intending that someone can read and understand this then you need to be careful about removing the round brackets since it can change the context

(A or B) AND C

is not the same as

A or B and C

which will be interpreted as

A or (B AND C)

and's and 'or's have the same priorities as +- and */ in maths

 

[Gasman]

Having seen the power of regular expressions, I would probably give them a go.

This pattern (\[|\]) would get rid of the square brackets.
Add extra characters with the | symbol.

Here is some code arnelgb created for an excel query, but works just as well in Access.

Public Function ReplaceText(ByVal pText As String, ByVal Pattern As String, Optional pReplacement As String = "~") As String

    With CreateObject("VBScript.RegExp")
    
        .Pattern = "(" & Pattern & ")"
        .Global = True
        .IgnoreCase = True
        
        ReplaceText = .Replace(pText, pReplacement)
        
    End With
    
End Function

and

? replacetext("[Field1]='text' AND [Field2] <> 1","\[|\]|<|>","-")

produces
-Field1-='text' AND -Field2- -- 1

I used an - replacement character

This site allows you to test the expression pattern
https://regex101.com/

HTH

 

[jeran042]

If you don't want the nested Replace statements, then you might try something like this:

Private Function MakeAlphaNumeric(ByVal FileNumber As String) As String

Dim x As Long
Dim CleanString As String
Dim CheckedChar As String
Dim AVal As Long

    For x = 1 To Len(FileNumber)
        CheckedChar = Mid(FileNumber, x, 1)
        AVal = Asc(CheckedChar)
        [COLOR=seagreen]'Ensure that only letters and numbers are included[/COLOR]
        If Not ( _
                    (AVal >= 48 And AVal <= 57) _
                    Or (AVal >= 65 And AVal <= 90) _
                    Or (AVal >= 97 And AVal <= 122) _
                    Or AVal = 131 Or AVal = 138 Or AVal = 140 Or AVal = 142 _
                    Or AVal = 154 Or AVal = 156 Or AVal = 158 Or AVal = 159 _
                    Or (AVal >= 192 And AVal <= 246) _
                    Or (AVal >= 248 And AVal <= 255) _
                ) Then
            CheckedChar = ""
        End If
        [COLOR=seagreen]'Add CheckedChar to CleanString[/COLOR]
        CleanString = CleanString & CheckedChar
    Next x
    
    MakeAlphaNumeric = CleanString
    
End Function

This strips out any character that is not a letter or number. It allows assorted letters that don't appear in English - that's why there are so many OR lines.

I use it as part of validation in a couple forms, as it would probably seriously slow down large queries.

Honestly, though, if you're looking for elegance, what you have is probably it. It's just hard to parse, that's all.

How would I call this function? When I was initially thinking about a better way, it resembled this. Although in my thought, it was an array of characters. But I would like to give this a try.

In addition, (slightly off topic) is there a way to replace the word "AND" with a like break?

 

[June7]

Can call the function in textbox, query, VBA.

=MakeAlphaNumeric([Forms]![frm: Report Creator]![frm:Ledger_Detail_Reports_Subform].[Form].[Filter])

 

[Frothingslosh]

How would I call this function? When I was initially thinking about a better way, it resembled this. Although in my thought, it was an array of characters. But I would like to give this a try.

Seriously, what you have already is almost certainly the fastest, most elegant way to do what you're looking for, and I'm with June and CJ_London that you should stick with what you have.

To answer your question, though, and assuming you're using it as a control source, you would use this:

=MakeAlphaNumeric(Forms![Report Creator]![Ledger_Detail_Reports_Subform].Form.Filter)

Keep in mind, however, that this function strips out ALL non-alphanumeric characters, including exclamation points, periods, and quotes, not just the brackets, parentheses, and apostrophes you're looking for. If any of these are included in the filter, then it would NOT be an actual representation of the currently applied filter.

 

[jeran042]

Based on all the responses I will keep what I have. But I really appreciate everyone's help.

@Frothingslosh I will definitely try (and likely use) this function in the future, I'm sure!

Thank you again

 

[Frothingslosh]

Oh, and you should probably rename the parameter just to make it more generic. Obviously that particular version is used for a very specific purpose.

 

[MajP]

Seriously, what you have already is almost certainly the fastest, most elegant way to do what you're looking for

I doubt it, but on a small db it is probably not measureable and not worth the effort. I would put my money on GasMans Regexp for any real size db. Mindboggling how fast regexp is. I just ran a demo on 15000 records to find 30 words and it was instantaneous.

 

[Frothingslosh]

Fair enough, I've never used RegExp, and actually had never even heard of it until a couple days ago.

 

[NauticalGent]

RegEx is probably the most comprehensive and efficient way to deal with text - incredibly powerful if not a little difficult to understand. That is for me anyway, some of you coding studs will take to it like a duck to water.

Steve (Snueburg) and I once did a time metric on the effects a recursive statement such as nested Replace() - I would post it here but I can’t do it at the moment, the wi-fi at the B&B is just about non-existent.

The rule was there is a slight performance hit but with small recordset it isn’t a big deal, but as MajP has alluded to, as the data grows it could become a factor.

 

[Gasman]

Well to be fair it is not as if the o/p was amending a lot of fields, just the one.
However I believe those few lines of code are far easier to read than all the nested functions.?

If I had to come back to that after a few weeks, it would take me a while to work it all out again.

 

[NauticalGent]

Found the old post:

https://www.access-programmers.co.uk/forums/showthread.php?t=292128&highlight=Recursive

 

[CJ_London]

interesting but the one comparison not timed is nested replaces within a query - i.e. no call to a udf

 

[mike7352]

Hi there, this thread is quite old but I have relevant question. I am trying to change this ugly yet functional nested replace function to use vbBinaryCompare so that it will only replace it if matches all CAPS. I cannot seem to get the syntax quite right.

=Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([hours],"|",""),"EVO-LS",""),"IND",""),"FOH",""),"ENT",""),"VEL",""),"Instrumal","Instrumental"),"EVO-KA",""),"rance","Entrance")

 

[ebs17]

Replace(expression, find, replace[, start[, count[, compare]]])

Immerse yourself in reading the documentation and be interested in the possible arguments.

 

[mike7352]

Thank you @ebs17, I was doing everything correctly, but needed to use 0 instead of vbBinaryCompare.

 

[ebs17]

vbBinaryCompare = 0

If Replace is executed in a query: Jet/ACE does not know VBA constants. Then you have to insert the value directly instead of the more descriptive name.