Solved

Replace function

Posted on 2009-05-15
18
315 Views
Last Modified: 2013-11-28
Found the Replace Function on MS website:

However I am having problems understanding why the variable of P does not return a value of greater than Zero.

see below for the code I am using to determne the search criteria>

Karen
Function Replace(ByVal strSearch As String, ByVal strSearchFor As _

                 String, ByVal strReplaceWith As String) As String

   Dim Temp As String, P As Long

   Temp = strSearch

   P = InStr(Temp, strSearchFor)

   Do While P > 0

      Temp = Left(Temp, P - 1) & strReplaceWith & _

          Mid(Temp, P + Len(strSearchFor))

      P = InStr(P + Len(strReplaceWith), Temp, strSearchFor, 1)

   Loop

   Replace = Temp

End Function
 

'********************************************************************************************************

Private Sub cmdCopyWSNo_Click()

Dim sFieldName, recValue, recValue1, strSearch1

Dim rs1 As Recordset

    

    Select Case Work_Code

        Case 1

            gWC = [Forms]![frmSR_Main]!Work_Code.Column(1)

        Case 3

            gWC = [Forms]![frmSR_Main]!Work_Code.Column(1)

    End Select

    gJG = [Forms]![frmSR_Main]![JobGroup]

    gLab = [Forms]![frmSR_Main]![Cmis_Lab]

    cntx = DCount("[RecID]", "tblEquipListingPerJobGroup", "[Job_group]= " & Chr(39) & gJG & Chr(39) & "")

    cntx1 = DCount("[RecID]", "tblEquipListTemp", "[Job_group]= " & Chr(39) & gJG & Chr(39) & "")

    Debug.Print cntx

    Debug.Print cntx1

    strSQL = "SELECT *" & _

            " FROM tblEquipListTemp" & _

            " WHERE (Job_Group=" & Chr(39) & gJG & Chr(39) & ")"

    Set rs = CurrentDb.OpenRecordset(strSQL)

    sFieldName = rs.Fields("Equipment_ID").Name & "  " & rs.Fields("MeasNo").Name & _

                    "  " & rs.Fields("WSNo").Name & ""

    

    strSQL1 = "SELECT *" & _

            " FROM tblEquipListingPerJobGroup" & _

            " WHERE (Job_Group=" & Chr(39) & gJG & Chr(39) & ")"

    Set rs1 = CurrentDb.OpenRecordset(strSQL1)
 

        If Nz(RequestorComments, vbNullString) = True Then

            If gLab = "F100" Then

                recValue = "Work Code:  " & gWC

                GoSub MyUpdate:

            Else

               'Data from tblEquipListTemp

                rs.MoveFirst

                    Do Until rs.EOF

                        recValue = recValue & vbCrLf & _

                                    rs!Equipment_ID & "           " & _

                                    rs!MeasNo & "    " & rs!WSNo & ""

                                    rs.MoveNext

                    Loop

                recValue = sFieldName & vbCrLf & recValue & vbCrLf & "Work Code:  " & gWC

                Forms![frmSR_Main]![RequestorComments].Value = recValue

                GoTo ResumeNext:

            End If

        

        ElseIf Len(Nz(Me.RequestorComments)) > 0 Then

            rs.MoveFirst

            Do Until rs.EOF

        'Data from tblEquipListTemp

                recValue = recValue & vbCrLf & _

                            rs!Equipment_ID & "           " & _

                            rs!MeasNo & "    " & rs!WSNo & ""

                rs.MoveNext

            Loop

            recValue = sFieldName & vbCrLf & recValue

        'Data from tblEquipListingPerJobGroup

            rs1.MoveFirst

            Do Until rs1.EOF

                recValue1 = recValue1 & vbCrLf & _

                            rs1!Equipment_ID & "           " & _

                            rs1!MeasNo & "    " & rs1!WSNo & ""

                rs1.MoveNext

            Loop

            recValue1 = sFieldName & vbCrLf & recValue1

            GoSub MyUpdate:

        End If

MyUpdate:

    'cntx = count of Records from tblEquipListingPerJobGroup

    'cntx1 = count of Records from tblEquipListTemp

    If cntx <> cntx1 Then

        strSearchFor = recValue1  'Data from tblEquipListingPerJobGroup

        Debug.Print strSearchFor

        strReplaceWith = recValue 'Data from tblEquipListTemp

        Debug.Print strReplaceWith

        strSearch = Forms![frmSR_Main]![RequestorComments]

    Else

        GoTo ResumeNext:

    End If

        Forms![frmSR_Main]![RequestorComments].Value = Replace(strSearch, strSearchFor, strReplaceWith)

    GoTo ResumeNext:

Return

ResumeNext:

    rs.Close

    Set rs = Nothing

    rs1.Close

    Set rs1 = Nothing

    Forms![frmSR_Main].RequestorComments.Requery

End Sub

Open in new window

0
Comment
Question by:Karen Schaefer
  • 9
  • 4
  • 2
  • +1
18 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24399955
Hello kfschaefer1,

Why are you making a UDF for Replace?  Access and VBA already have native Replace functions that
operate much the same way...

Regards,

Patrick
0
 

Author Comment

by:Karen Schaefer
ID: 24399968
This is what I found at Microsoft's website.

How would you modify my code to get the same results?

K
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24399986
For starters I would delete the UDF Replace--VBA's Replace function works just fine.

Then, replace:

        Forms![frmSR_Main]![RequestorComments].Value = Replace(strSearch, strSearchFor, strReplaceWith)

with:

        Debug.Print "strSearch: " & strSearch & ", strSearchFor:" & strSearchFor & ", strReplaceWith: " & strReplaceWith
        Forms![frmSR_Main]![RequestorComments].Value = Replace(strSearch, strSearchFor, strReplaceWith)

and let us know what prints to the immediate window.
0
 

Author Comment

by:Karen Schaefer
ID: 24400001
strSearch:
Equipment_ID  MeasNo  WSNo
Test1               A101A
Test2               A101A
Work Code:  Work in lab

this is a test of the code.,

strSearchFor:
Equipment_ID  MeasNo  WSNo

Test1               A101A
Test4               A101A
Test3               A101A
Test2               A101A,

strReplaceWith:
Equipment_ID  MeasNo  WSNo

Test1               A101A
Test4               A101A
Test3               A101A
Test2               A101A
Test31               A101A
Test41               A101A


The code doesn't replace the correct value - it appears nothing changed.

Karen
0
 

Author Comment

by:Karen Schaefer
ID: 24400010
Posted screen Shot of sample.
kfsSample.png
0
 

Author Comment

by:Karen Schaefer
ID: 24413252
Still looking for help.
0
 

Author Comment

by:Karen Schaefer
ID: 24413302
Does the Replace function use a wildcard search or just  the literal search of the field?

K
0
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24417205
It depends on the options you specify. (See attached documentation)  This is also true of the function "Replace"  you which provided.  You need to make sure that you specifcy vbTextCompare in either function.  This could be what is cauing your unpredictable results.  

In you function Replace this option would be specified in the instr() function as follows:

P = InStr(1, Temp, strSearchFor, vbTextCompare)

in the standard Replace function it looks like this:

Replace(strSearch, strSearchFor, strReplaceWith, 1, , vbTextCompare)


Replace Function
     

Description

Returns a string in which a specified substring has been replaced with another substring a specified number of times.

Syntax

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

The Replace function syntax has these named arguments:

Part Description
expression Required. String expression containing substring to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If omitted, the default value is 1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.



Settings

The compare argument can have the following values:

Constant Value Description
vbUseCompareOption 1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.



Return Values

Replace returns the following values:

If Replace returns
expression is zero-length Zero-length string ("")
expression is Null An error.
find is zero-length Copy of expression.
replace is zero-length Copy of expression with all occurences of find removed.
start > Len(expression) Zero-length string.
count is 0 Copy of expression.



Remarks

The return value of the Replace function is a string, with substitutions made, that begins at the position specified by start and and concludes at the end of the expression string. It is not a copy of the original string from start to finish.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 38

Expert Comment

by:Jim P.
ID: 24417250
The Replace is literal

?Replace("this and that","that","those")
this and those

What are you trying to get to? Can you give us an example of what you are starting with and what you want as a result?
0
 

Author Comment

by:Karen Schaefer
ID: 24417340
Thanks for the advice,

Here it is in a nutshell.  NOTE: MOST OF MY VARIABLES ARE SET GLOBALLY

see the Screenshot.

I need to duplicate the data in the Equipment Detail (subform) into the Requestors Comments (memo), along with the value of the Work Code field, plus any additional comments added by the user manually.  so if the detail has been updated then it needs to compare with the data already existing in the memo field and make the modifications.

The datasource for the subform is a temp table and does not update the main data table until the user goes to new record or clicks on the Save Detail button.  If this is clicked then the data from the temp table is update after the Memo field is updated, including the comments previously added.

Please note the values for the search criteria of the Replace function (Default function w/i Access) not the 1 previously mentioned.

thanks for your assistance,  Unfortunately, it is the end of the day for me so I will pick it up in the morning.

Feel free to rewrite everything if there is a simplier way of doing things.

to reiterate:  i just need to compare the values in the memo field and make sure they match the Equipment Detail and the Work code, plus the manually added comments by the user.

thanks,

Karen
strSearch:

Equipment_ID  MeasNo  WSNo

Test1               A101A

Test2               A101A

Work Code:  Work in lab
 

this is a test of the code.,
 

strSearchFor:

Equipment_ID  MeasNo  WSNo
 

Test1               A101A

Test4               A101A

Test3               A101A

Test2               A101A,
 

strReplaceWith:

Equipment_ID  MeasNo  WSNo
 

Test1               A101A

Test4               A101A

Test3               A101A

Test2               A101A

Test31               A101A

Test41               A101A
 

'*********************************************************************

Private Sub cmdCopyWSNo_Click()

Dim sFieldName, recValue, recValue1, strSearch1

Dim rs1 As Recordset

    

    Select Case Work_Code

        Case 1

            gWC = [Forms]![frmSR_Main]!Work_Code.Column(1)

        Case 3

            gWC = [Forms]![frmSR_Main]!Work_Code.Column(1)

    End Select

    gJG = [Forms]![frmSR_Main]![JobGroup]

    gLab = [Forms]![frmSR_Main]![Cmis_Lab]

    strSQL = "SELECT *" & _

            " FROM tblEquipListTemp" & _

            " WHERE (Job_Group=" & Chr(39) & gJG & Chr(39) & ")"

    Set rs = CurrentDb.OpenRecordset(strSQL)

    sFieldName = rs.Fields("Equipment_ID").Name & "  " & rs.Fields("MeasNo").Name & _

                    "  " & rs.Fields("WSNo").Name & ""

    

    strSQL1 = "SELECT *" & _

            " FROM tblEquipListingPerJobGroup" & _

            " WHERE (Job_Group=" & Chr(39) & gJG & Chr(39) & ")"

    Set rs1 = CurrentDb.OpenRecordset(strSQL1)

    If rs1.RecordCount = 0 Or Len(Nz(Me.RequestorComments)) > 0 Then

        If gLab = "F100" Then

            recValue = "Work Code:  " & gWC

            GoSub MyUpdate

        Else

           'Data from tblEquipListTemp

            rs.MoveFirst

                Do Until rs.EOF

                    recValue = recValue & vbCrLf & _

                                rs!Equipment_ID & "           " & _

                                rs!MeasNo & "    " & rs!WSNo & ""

                                rs.MoveNext

                Loop

            recValue = sFieldName & vbCrLf & recValue & vbCrLf & "Work Code:  " & gWC

            Debug.Print recValue

            Forms![frmSR_Main]![RequestorComments].Value = recValue

            GoTo ResumeNext:

        End If

        

    ElseIf Len(Nz(Me.RequestorComments)) > 0 Then

        rs.MoveFirst

        Do Until rs.EOF

    'Data from tblEquipListTemp

            recValue = recValue & vbCrLf & _

                        rs!Equipment_ID & "           " & _

                        rs!MeasNo & "    " & rs!WSNo & ""

            rs.MoveNext

        Loop

        recValue = sFieldName & vbCrLf & recValue

    'Data from tblEquipListingPerJobGroup

        rs1.MoveFirst

        Do Until rs1.EOF

            recValue1 = recValue1 & vbCrLf & _

                        rs1!Equipment_ID & "           " & _

                        rs1!MeasNo & "    " & rs1!WSNo & ""

            rs1.MoveNext

        Loop

        recValue1 = sFieldName & vbCrLf & recValue1

        GoSub MyUpdate

    End If

MyUpdate:

    If rs.RecordCount <> rs1.RecordCount Then

        strSearchFor = recValue1  'Data from tblEquipListingPerJobGroup

        strReplaceWith = recValue 'Data from tblEquipListTemp

        strSearch = Forms![frmSR_Main]![RequestorComments]

    Else

        GoTo ResumeNext:

    End If

        gValue = Replace(strSearch, strSearchFor, strReplaceWith)

        Forms![frmSR_Main]![RequestorComments].Value = gValue

    GoTo ResumeNext:

Return

ResumeNext:

    rs.Close

    Set rs = Nothing

    rs1.Close

    Set rs1 = Nothing

    Forms![frmSR_Main].RequestorComments.Requery

    Me.Refresh

End Sub

Open in new window

0
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24417491
Two suggestions.

1.
Change:
gValue = Replace(strSearch, strSearchFor, strReplaceWith)

To:
gValue = Replace(strSearch, strSearchFor, strReplaceWith, 1, , vbTextCompare)

2.
If your memo fields are rather large, you may eventually run into the situation where GetChunk is required to read all of the text in them.  

If this does not help, please say specifically what is not currently working in you code :)
0
 

Author Comment

by:Karen Schaefer
ID: 24423260
Thanks for the suggestion, however, the gValue = Replace(strSearch, strSearchFor, strReplaceWith, 1, , vbTextCompare)
Still removes the manually entered text from the memo field, instead of just replacing the Detail and Work Code info.

Got any other ideas?

K
0
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24423569
What happens to the data in the table itself? Is it just the control that goes blank or does your code delete the memo fields value in the table?
0
 

Author Comment

by:Karen Schaefer
ID: 24423632
Not until the user exits the record or selects the Save Detail button.  If the user chooses to select "CopyWSNo" button then just the attached codes runs.  W/i that I am comparing the record count of both tables and if different then proceed with the update of the Memo field.  I know this could be an issue but unsure how to handle that without rewritting the process for the subform.  which is not an option at this time because that would be a major rewrite.

The data from the subform is stored in an actual temp table not a recordset and the actual data table is not updated until the SaveDetail is selected.

How this explains it better.

K
0
 
LVL 3

Expert Comment

by:jakemdrew
ID: 24424348
Can you place the following code below the line gValue = Replace(strSearch, strSearchFor, strReplaceWith) and tell me what the message box says:

msgbox "strSearch = " & strSearch & vbnewline & _
             "strSearchFor = " & strSearchFor & vbnewline & _
             "strReplaceWith = " & strReplaceWith & vbnewline & _
             "gValue = " & gValue
 


0
 

Accepted Solution

by:
Karen Schaefer earned 0 total points
ID: 24425766
thanks for your assistance I was able to get the answer internally, since it seemed to be to difficult to illustrate the actual problems I was having.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now