Replace function (2003 sp3)

Topic

New Reply

I cannot get this SQL to work and I know the error is related to my use of the replace function with parAO. The problem which prompted the use of Relace was a name with an apostrophe in it, like O’Donnell. The SQL:

parAO = Forms!frm_COMPLETING_REVIEWS.AO
parAO = Replace(parAO, ” ‘ “, ” ‘ ‘ “)
parAO = ” ” & parAO & ” ”
strSQL5 = “INSERT INTO tbl_COMPLETED_REVIEWS (ASSET, NEW_APPRAISAL_VALUE, NEW_PRICE,” _
& “NEW_APPRAISAL_DATE, NEW_INSPECTION_DATE, NEW_REVIEW_DATE, NEW_COMMENTS, NEW_DISPOSITION) ” _
& “SELECT tbl_ANNUAL_REVIEWS_SENT.ASSET_NUMBER, tbl_ANNUAL_REVIEWS_SENT.APPRAISED_VALUE,” _
& “tbl_ANNUAL_REVIEWS_SENT.UPDATE_PRICE, tbl_ANNUAL_REVIEWS_SENT.NEW_APPRAISAL_DATE,” _
& “tbl_ANNUAL_REVIEWS_SENT.NEW_INSPECTION_DATE, tbl_ANNUAL_REVIEWS_SENT.NEW_REVIEW_DATE,” _
& “tbl_ANNUAL_REVIEWS_SENT.NEW_COMMENTS, tbl_ANNUAL_REVIEWS_SENT.NEW_DISOPSITION ” _
& “FROM tbl_ANNUAL_REVIEWS_SENT WHERE (tbl_ANNUAL_REVIEWS_SENT.ASSET_NUMBER)= ” & parAsset & ” ” _
& “AND (tbl_ANNUAL_REVIEWS_SENT.AO)= ” & parAO & ” “

Reply | Quote

Replies

Try this; note that the lines manipulating parAO have been removed:

parAO = Forms!frm_COMPLETING_REVIEWS.AO
strSQL5 = “INSERT INTO tbl_COMPLETED_REVIEWS (ASSET, NEW_APPRAISAL_VALUE, NEW_PRICE,” _
& “NEW_APPRAISAL_DATE, NEW_INSPECTION_DATE, NEW_REVIEW_DATE, NEW_COMMENTS, NEW_DISPOSITION) ” _
& “SELECT tbl_ANNUAL_REVIEWS_SENT.ASSET_NUMBER, tbl_ANNUAL_REVIEWS_SENT.APPRAISED_VALUE,” _
& “tbl_ANNUAL_REVIEWS_SENT.UPDATE_PRICE, tbl_ANNUAL_REVIEWS_SENT.NEW_APPRAISAL_DATE,” _
& “tbl_ANNUAL_REVIEWS_SENT.NEW_INSPECTION_DATE, tbl_ANNUAL_REVIEWS_SENT.NEW_REVIEW_DATE,” _
& “tbl_ANNUAL_REVIEWS_SENT.NEW_COMMENTS, tbl_ANNUAL_REVIEWS_SENT.NEW_DISOPSITION ” _
& “FROM tbl_ANNUAL_REVIEWS_SENT WHERE (tbl_ANNUAL_REVIEWS_SENT.ASSET_NUMBER)=” & parAsset & ” ” _
& “AND (tbl_ANNUAL_REVIEWS_SENT.AO)=” & Chr(34) & parAO & Chr(34)

Chr(34) is the double quote character “.

Tip: if you insert a line

MsgBox strSQL5

into your code during testing, you can see the result, and inspect it for problems. That’s what I did to find out what strSQL5 looked like. You should of course remove or comment out the MsgBox later on.

Reply | Quote

Well my friend, you have never failed me yet. Thanks especially for the MsgBox tip.

ps I am retired now but still dabbling. The first thing I did for my predecessor is introduce him to Woody’s Lounge.

Reply | Quote