This routine parses a string and returns an equivalent string where all the instances of a given substring are doubled. This is especially useful for doubling quotes within SQL statements to be passed to Access or other database engines that expect double quote marks to represent a single quote mark:
Public Function DoubleString (_ ByVal str As String, _ ByVal strDoubleString As String) As String Dim intStringLength As Integer Dim intDoubleStringLength As Integer Dim intPosition As Integer Dim strTemp As String intStringLength = Len(str) intDoubleStringLength = _ Len(strDoubleString) strTemp = str If intStringLength >= _ intDoubleStringLength And _ intDoubleStringLength > 0 Then intPosition = 1 Do While (intPosition > 0) And _ (intPosition <= intStringLength) intPosition = InStr(intPosition, _ strTemp, strDoubleString) If intPosition > 0 Then strTemp = Left(strTemp, _ intPosition - 1 + _ intDoubleStringLength) & _ strDoubleString & _ Mid(strTemp, intPosition + _ intDoubleStringLength, _ intStringLength) intStringLength = Len(strTemp) intPosition = intPosition + _ (intDoubleStringLength * 2) End If Loop End If DoubleString = strTempEnd Function