Remove Quotes within Data Values

[kfschaefer]

I need a way to prevent a double quote placed where there should be a single (apostrophe) in the data value.
See '>>>>>>>>
INSERT INTO TL_FTEM
( FirstofEquipment_ID
, NOMENCLATURE
, Nomenclature_Modifier
, EQPT_LOC_NO
, SERVICE_ORGN_CODE
, CountOfEQUIPMENT_ID
, SERVICE_DUE_DATE_CMT
, LAST_SERVICE_DATE
, Manufacturer
, Model
, VendorPart
, RANGE
)
VALUES('FTX 17749'
,'TRANSD,POS,ROTS'
,'SYNCHRO 8 SH SH'
,'TIS-VCE'
,'4100'
,'1'
,'4/29/1988'
,'4/10/1983'
,'test'
,'65Y13380-009'
,'706'
'>>>>>>> ,'.15"" SHORTSHAFT'
)

Thanks for all the input.

Karen
Here is my code so far:

Public Function FTCSConnection()
Dim sConn As String
Dim oConn As ADODB.Connection
Dim rstOra As ADODB.Recordset, rs As ADODB.Recordset
Dim adoRS As ADODB.Recordset
Dim cn As ADODB.Connection
Dim ctl As Control
Dim J, I As Long
Dim rsField, tblField As String
Dim rsValue, tblValue As String
Dim varLSD As Variant ' Last Service Date
Dim varRNG As Variant ' Range
Dim varSDD As Variant ' Service Due Date Cmt
Dim varEQL As Variant ' Equipment Location
Set cn = CurrentProject.Connection

sConn = _
"Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
"(Host=Service1.com)(Port=1521)))(CONNECT_DATA=
(SID=ftcsprod)));" & _
"User Id=Test1;Password=pswd4;"
Set adoConn = New ADODB.Connection
adoConn.Open sConn
Set adoRS = New ADODB.Recordset
strSQL = "Delete * from TL_FTEM"
CurrentProject.Connection.Execute strSQL
Set rs = New ADODB.Recordset
strSQL = "Select * from TL_FTEM"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
'" IIf(IsNull([Service_Due_Date_CMT]),Null,DateSerial(Left
([Service_Due_Date_CMT],2),Mid([Service_Due_Date_CMT],3,2),Right
([Service_Due_Date_CMT],2))) AS ServDueDate," & _

strSQL = " SELECT DISTINCT FTEM_ID AS FirstofEquipment_ID,
EQPT_NAME AS NOMENCLATURE, NOMEN_MODIFIER_NAME AS
Nomenclature_Modifier," & _
" EQPT_LOC_NO, SERVICE_ORGN_CODE,COUNT(*) AS
CountOfEQUIPMENT_ID," & _
" SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, MFR_NAME AS
Manufacturer, MFR_NO AS Model, PART_VENDOR_SERIAL_NO AS VendorPart,
RANGE" & _
" FROM FTCSPROD.FTEM_VI_VW" & _
" GROUP BY FTEM_ID, EQPT_NAME, NOMEN_MODIFIER_NAME,
EQPT_LOC_NO, SERVICE_ORGN_CODE," & _
" Service_Due_Date_CMT," & _
" LAST_SERVICE_DATE, MFR_NAME, MFR_NO,
PART_VENDOR_SERIAL_NO, RANGE" & _
" HAVING (((SERVICE_ORGN_CODE) Is Not Null))" & _
" ORDER BY FTEM_ID"
Set adoRS = New ADODB.Recordset
adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly
adoRS.MoveFirst
Do Until adoRS.EOF
If Not IsNull(adoRS("EQPT_LOC_NO")) And adoRS("EQPT_LOC_NO") <>
"" Then
If InStr(adoRS("EQPT_LOC_NO"), "'") = 0 Then
varEQL = Replace(adoRS("EQPT_LOC_NO"), Chr(34), Chr
(34) & Chr(34))
ElseIf InStr(adoRS("EQPT_LOC_NO"), "''") = 0 Then
varEQL = Replace(adoRS("EQPT_LOC_NO"), "'", "''")
End If
End If
If Not IsNull(adoRS("SERVICE_DUE_DATE_CMT")) And adoRS
("SERVICE_DUE_DATE_CMT") <> "" Then
varSDD = Format(DateSerial(Left(adoRS
("SERVICE_DUE_DATE_CMT"), 2), Mid(adoRS("SERVICE_DUE_DATE_CMT"), 3,
2), Right(adoRS("SERVICE_DUE_DATE_CMT"), 2)), "Short Date")
Else
varSDD = adoRS("SERVICE_DUE_DATE_CMT")
End If
If Not IsNull(adoRS("LAST_SERVICE_DATE")) And adoRS
("LAST_SERVICE_DATE") <> "" Then
varLSD = Format(CDate(adoRS("LAST_SERVICE_DATE")), "Short
Date")
Else
varLSD = adoRS("LAST_SERVICE_DATE")
End If
If Not IsNull(adoRS("Range")) And adoRS("Range") <> "" Then
If InStr(adoRS("Range"), "'") = 0 Then
varRNG = Replace(adoRS("Range"), Chr(34), Chr(34) &
Chr
(34))
ElseIf InStr(adoRS("Range"), "''") = 0 Then
varRNG = Replace(adoRS("Range"), "'", "''")
End If
End If
strSQL = "INSERT INTO TL_FTEM" & vbCrLf & _
" ( FirstofEquipment_ID" & vbCrLf & _
" , NOMENCLATURE" & vbCrLf & _
" , Nomenclature_Modifier" & vbCrLf & _
" , EQPT_LOC_NO" & vbCrLf & _
" , SERVICE_ORGN_CODE" & vbCrLf & _
" , CountOfEQUIPMENT_ID" & vbCrLf & _
" , SERVICE_DUE_DATE_CMT" & vbCrLf & _
" , LAST_SERVICE_DATE" & vbCrLf & _
" , Manufacturer" & vbCrLf & _
" , Model" & vbCrLf & _
" , VendorPart" & vbCrLf & _
" , RANGE" & vbCrLf & _
" )" & vbCrLf

strSQL = strSQL & _
" VALUES('" & Nz(adoRS("FirstofEquipment_ID")) & "'"
& vbCrLf & _
" ,'" & Nz(adoRS("NOMENCLATURE")) & "'" &
vbCrLf & _
" ,'" & Nz(adoRS("Nomenclature_Modifier")) &
"'" & vbCrLf & _
" ,'" & varEQL & "'" & vbCrLf & _
" ,'" & Nz(adoRS("SERVICE_ORGN_CODE")) & "'" &
vbCrLf & _
" ,'" & Nz(adoRS("CountOfEQUIPMENT_ID")) & "'"
& vbCrLf & _
" ,'" & varSDD & "'" & vbCrLf & _
" ,'" & varLSD & "'" & vbCrLf & _
" ,'" & Nz(adoRS("Manufacturer")) & "'" &
vbCrLf & _
" ,'" & Nz(adoRS("Model")) & "'" & vbCrLf & _
" ,'" & Nz(adoRS("VendorPart")) & "'" & vbCrLf
& _
" ,'" & varRNG & "'" & vbCrLf & _
" )"

Debug.Print strSQL
CurrentProject.Connection.Execute strSQL
adoRS.MoveNext
Loop

End Function

 

[HiTechCoach]

Try using the Replace() function

 

[kfschaefer]

I am already using the Replace function, however when the data value contains a apostrophe or double quotes for the representation of inches (ie. 48" Part)

It seems to either replace the double quotes with more double quotes ie. (48"" Part)

Or if the single quote is used as an apostrophe then it replaces it with the double quote. (ie. Pilot's Command, Pilot"s Command)

I also tried to use the following and still does not return the correct results.

If Not IsNull(adoRS("Range")) And adoRS("Range") <> "" Then
varRNG = Replace(adoRS("Range"), "'", "''") 'double up single quotes
varRNG = Replace(adoRS("Range"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
varRNG = LTrim(varRNG)
End If

 

[HiTechCoach]

I am already using the Replace function,

It seems to either replace the double quotes with more double quotes ie. (48"" Part)

Or if the single quote is used as an apostrophe then it replaces it with the double quote. (ie. Pilot's Command, Pilot"s Command)

I also tried to use the following and still does not return the correct results.

If Not IsNull(adoRS("Range")) And adoRS("Range") <> "" Then
varRNG = Replace(adoRS("Range"), "'", "''") 'double up single quotes
varRNG = Replace(adoRS("Range"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
varRNG = LTrim(varRNG)
End If

however when the data value contains a apostrophe or double quotes for the representation of inches (ie. 48" Part)

apostrophe = feet
quotes = inches

Are you saying he the user will type two apostrophes for inches and not use the quotes.

If this is true, first use:

varRNG = Replace(adoRS("Range"), Chr(39) & Chr(39), Chr(34))   ' convert two apostrophes to a quote

Now you can process the data as needed.