String function in script

[#1]

Hi all,

I would like to use and call this function from the load script, instead of using a macro.

I have a field called 'Comments' which I'd like to use as myString.

  Function maskUsers(inText As String) As String

    Dim myString As String

    Dim myPattern As String

    Dim myPosition As Long

    Dim myFlag As Boolean

    myString = inText

    myPattern = "[a-dA-D]######"

    For myPosition = 1 To Len(myString) - 6

        myFlag = False

        If Mid(myString, myPosition, 7) Like myPattern Then

            ''''Pattern matched

            myFlag = True

            ''''Ignore false positive based upon preceding alphabetic

            If myPosition > 1 Then

                If Mid(myString, myPosition - 1, 1) Like "[a-zA-Z]" Then myFlag = False

            End If

            ''''Ignore false positive based upon trailing numeric

            If myPosition < Len(myString) - 7 Then

                If Mid(myString, myPosition + 7, 1) Like "[0-9]" Then myFlag = False

            End If

            ''''Mask matched pattern

            If myFlag Then

                myString = Left(myString, myPosition - 1) & "*******" & Right(myString, Len(myString) - myPosition - 6)

            End If

        End If

    Next myPosition

    maskUsers = myString

End Function

I'd like to know it is is possible to Call this function in the script load, as I know it is possible.

Many thanks

[#2]

for call the function inscript write this syntax in script.

load *,

maskUsers(fieldname) As String

from table;

and in macro write this below syntax

Function maskUsers(inText)

    Dim myString As String

    Dim myPattern As String

    Dim myPosition As Long

    Dim myFlag As Boolean

    myString = inText

    myPattern = "[a-dA-D]######"

    For myPosition = 1 To Len(myString) - 6

        myFlag = False

        If Mid(myString, myPosition, 7) Like myPattern Then

            ''''Pattern matched

            myFlag = True

            ''''Ignore false positive based upon preceding alphabetic

            If myPosition > 1 Then

                If Mid(myString, myPosition - 1, 1) Like "[a-zA-Z]" Then myFlag = False

            End If

            ''''Ignore false positive based upon trailing numeric

            If myPosition < Len(myString) - 7 Then

                If Mid(myString, myPosition + 7, 1) Like "[0-9]" Then myFlag = False

            End If

            ''''Mask matched pattern

            If myFlag Then

                myString = Left(myString, myPosition - 1) & "*******" & Right(myString, Len(myString) - myPosition - 6)

            End If

        End If

    Next myPosition

    maskUsers = myString

End Function

[#3]

Hi Vish,

Thanks for your reply.

I tried the macro in my example, but it returns an error - Expected End of Statement in the Macro module editor.

Do you perhaps know what could be causing this?

Many thanks

[er_mohit]

Areyou trying to do System Accesss in macro

and ctrl+Alt+M For system access in macro also

hope it helps

[#5]

Hi Vish,

Yes, I am using System Access.

It says Expected end of statement and highlights the word 'As String' at top, next to the declaration of variable

[#6]

The script looks all correct, but gives the error when I try run.

Any ideas?

[#7]

Ideas, on why I get an error when I try run it from the macro editor?

[#8]

I'm still trying to figure out what I'm doing wrong with this function, but I don't have much experience with VBScript.

It may be that the variables have to be declared differenty in QV.

Function maskUsers(inText)

    Dim myString As String

    Dim myPattern As String

    Dim myPosition As Long

    Dim myFlag As Boolean

    myString = inText

    myPattern = "[a-dA-D]######"

    For myPosition = 1 To Len(myString) - 6

        myFlag = False

        If Mid(myString, myPosition, 7) Like myPattern Then

            ''''Pattern matched

            myFlag = True

            ''''Ignore false positive based upon preceding alphabetic

            If myPosition > 1 Then

                If Mid(myString, myPosition - 1, 1) Like "[a-zA-Z]" Then myFlag = False

            End If

            ''''Ignore false positive based upon trailing numeric

            If myPosition < Len(myString) - 7 Then

                If Mid(myString, myPosition + 7, 1) Like "[0-9]" Then myFlag = False

            End If

            ''''Mask matched pattern

            If myFlag Then

                myString = Left(myString, myPosition - 1) & "*******" & Right(myString, Len(myString) - myPosition - 6)

            End If

        End If

    Next myPosition

    maskUsers = myString

End Function

It works like a charm in Excel, but keeps throwing an error at the top - 'Expected end of statement'

???