Nested REPLACE Functions (1 Viewer)

jeran042 · Jul 20, 2018

Good morning all,

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

Code:

=[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:

Code:

=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 · Jul 20, 2018

That's what I would use.

Frothingslosh · Jul 20, 2018

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

Code:

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 · Jul 20, 2018

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 · Jul 20, 2018

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.

Code:

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

Code:

? 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 · Jul 20, 2018

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

Code:

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 · Jul 20, 2018

Can call the function in textbox, query, VBA.

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

Frothingslosh · Jul 20, 2018

jeran042 said:
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:

Code:

=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 · Jul 20, 2018

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 · Jul 20, 2018

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 · Jul 21, 2018

Code:

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 · Jul 21, 2018

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

NauticalGent · Jul 21, 2018

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 · Jul 21, 2018

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 · Jul 21, 2018

Found the old post:

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

CJ_London · Jul 22, 2018

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

mike7352 · Feb 3, 2023

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.

Code:

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

ebs17 · Feb 3, 2023

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

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

mike7352 · Feb 3, 2023

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

ebs17 · Feb 3, 2023

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.

Nested REPLACE Functions (1 Viewer)

Registered User.

AWF VIP

Premier Pale Stale Ale

Super Moderator

Enthusiastic Amateur

Registered User.

AWF VIP

Premier Pale Stale Ale

Registered User.

Premier Pale Stale Ale

You've got your good things, and you've got mine.

Premier Pale Stale Ale

Ignore List Poster Boy

Enthusiastic Amateur

Ignore List Poster Boy

Super Moderator

New member

Well-known member

New member

Well-known member

Similar threads

Users who are viewing this thread