Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Replace function (2003 sp3)

    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 & " "
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace function (2003 sp3)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Replace function (2003 sp3)

    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.
    Thanks
    chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •