Karen Schaefer
asked on
Replace function
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
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
ASKER
This is what I found at Microsoft's website.
How would you modify my code to get the same results?
K
How would you modify my code to get the same results?
K
For starters I would delete the UDF Replace--VBA's Replace function works just fine.
Then, replace:
Forms![frmSR_Main]![Reques torComment s].Value = Replace(strSearch, strSearchFor, strReplaceWith)
with:
Debug.Print "strSearch: " & strSearch & ", strSearchFor:" & strSearchFor & ", strReplaceWith: " & strReplaceWith
Forms![frmSR_Main]![Reques torComment s].Value = Replace(strSearch, strSearchFor, strReplaceWith)
and let us know what prints to the immediate window.
Then, replace:
Forms![frmSR_Main]![Reques
with:
Debug.Print "strSearch: " & strSearch & ", strSearchFor:" & strSearchFor & ", strReplaceWith: " & strReplaceWith
Forms![frmSR_Main]![Reques
and let us know what prints to the immediate window.
ASKER
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
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
ASKER
Posted screen Shot of sample.
kfsSample.png
kfsSample.png
ASKER
Still looking for help.
ASKER
Does the Replace function use a wildcard search or just the literal search of the field?
K
K
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.
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.
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?
?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?
ASKER
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
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
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 :)
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 :)
ASKER
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
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
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?
ASKER
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
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
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
msgbox "strSearch = " & strSearch & vbnewline & _
"strSearchFor = " & strSearchFor & vbnewline & _
"strReplaceWith = " & strReplaceWith & vbnewline & _
"gValue = " & gValue
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why are you making a UDF for Replace? Access and VBA already have native Replace functions that
operate much the same way...
Regards,
Patrick