Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Insert query- doesn't like " or ' in memo field

    Hello there,

    I'm using the code below to insert into a tbl.

    Private Sub Category_DblClick(Cancel As Integer)
    Dim SQLtext
    Dim Ref_ID
    Ref_ID = Forms.main.Ref_ID

    If MsgBox("Add to Order" & Forms.main.Ref_ID & " ? ", vbYesNo, FullDirectory) = vbYes Then

    SQLtext = " INSERT INTO tbl_Orders (fkeyPSA_ID,Item,Part_No,Description,Category,Sub_ Category,List_Price)" & _
    " VALUES (" & _
    "'" & Ref_ID & "'" & _
    ",'" & Item & "'" & _
    ",'" & Part_No & "'" & _
    ",'" & Description & "'" & _ ' memo field
    ",'" & Category & "'" & _
    ",'7'" & _
    "," & List_Price & _
    ")"



    For some reason, if I have " or ' in the Description memo field for example Tom's toy or "text in quotes" I get run-time error 3075. Syntax error missing operator in query expression. If I change the text in the memo field to Tom’s toy the query runs fine?? notice the difference between ' and ’

    The same goes for " if i take them out of the memo it works fine

    I'm wondering whether i have constructed my query properly?

    thanks
    Mark

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    text/string values need to be encapsulated with either a ' or a " to tell the SQL engine where the values start and end. so the problem is in your data
    you need to replace any symbols in the text string that would indicate the datastream ends.
    so if you are using ' as your delimiter, then you must make sure no ' in the datastream
    you could use the replace function

    Code:
    SQLtext = " INSERT INTO tbl_Orders (fkeyPSA_ID,Item,Part_No,Description,Category,Sub_ Category,List_Price)" & _
    " VALUES (" & _
     & Ref_ID  & _ 
    ",'" & Item & "'" & _ 
    ",'" & Part_No & "'" & _
    ",'" & replace(Description, "'", "''") & "' & _ 
    ",'" & Category & "'" & _
    ",'7'" & _
    "," & List_Price & _
    ")"
    using the " symbol to delimit data youneed to replace all " with two "'s
    ',"' & replace(Description, '"', '""') & '"' & _

    note you do not need to encapsulate numeric values, in fact they should not be encapsulated as their is a risk the SQL engine may interpret them as string not number
    Last edited by healdem; 12-06-10 at 10:31.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks healdem,

    I must say my eyes are not as good as the used to be so I just tried to put your code into my vba.

    SQLtext = " INSERT INTO tbl_Orders (fkeyPSA_ID,Item,Part_No,Description,Category,Sub_ Category,List_Price)" & _
    " VALUES (" & _
    & Ref_ID & _
    ",'" & Item & "'" & _
    ",'" & Part_No & "'" & _
    ",'" & replace(Description, "'", "''") & "' & _
    ",'" & Category & "'" & _
    ",'7'" & _
    "," & List_Price & _
    ")"

    It would not compile. It highlighted the & at the begining of Ref_ID. Could you have a quick look please?

    thanks
    marcus

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its air code, Ive not checked, validated or compiled it
    removed the & before the Ref _ID
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi healdem,

    I'm really struggling with the code. I can't seem to get it to work. could you offer any help?
    thanks
    Mark

  6. #6
    Join Date
    Oct 2010
    Posts
    51
    What is the SQL you have in your code?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by marcusmacman View Post
    Hi healdem,

    I'm really struggling with the code. I can't seem to get it to work. could you offer any help?
    thanks
    Mark
    so what error messages are you getting
    what is the code you are running
    what steps have you taken to demostrate the SQL is valid
    have you tried runnign that SQL in say the query designer (SQL view) and see what works
    as said earlier I can spot a fault in the SQL I provided
    Code:
    SQLtext = " INSERT INTO tbl_Orders (fkeyPSA_ID,Item,Part_No,Description,Category,Sub_ Category,List_Price)" & _
    " VALUES (" & _
    Ref_ID  & _ 
    ",'" & Item & "'" & _ 
    ",'" & Part_No & "'" & _
    ",'" & replace(Description, "'", "''") & "' & _ 
    ",'" & Category & "'" & _
    ",'7'" & _
    "," & List_Price & _
    ");"
    I'd suggest you put a break point / watch on the SQLText line
    or use msgbox SQLText to dispaly the SQL you are sending to the engine.,
    the '7' looks highly suspicious to me

    so over to you
    try to investigate whats happening.. because that's the only way you are going to develop debugging skills which are an absolute must have for any developer
    ACC: Tips for Debugging Visual Basic for Applications Code

    by all means come back if you are stuill struggling, but make an effort to understand what is going on
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi healdem,

    I think you hit the nail on the head. I don't know what's going on!!! I spent 8hours + searching the internet trying different options. I do post a lot of questions on this site but that is because I am genuinely stuck. It frustrates me that I have to post, on the positive side hopefully I'm asking questions for other newbies like me.

    Back to the problem...I can't even get the code to compile (expected end of statement errors)

    I really do not understand how many " or ' or , or _ or & it's driving me nuts!!!

    I actually inherited the code you see below from some kind person on this site.

    I'm not sure what the replace function is doing. Is it looking at the data in the memo description field and replacing the " or ' with something else???

    or its it telling the sqlstring to look for "" for the start and end points???


    I had an idea regarding the original problem with " and ' in the description memo field. And that is when the user writes a description in the memo field I could trigger an event on close or something, which replaces the " and ' with ’and ’’
    What do you think?

    marcus
    Last edited by marcusmacman; 12-09-10 at 07:02.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you need to brush up on your debugging skills
    the message is telling you that it expected the end of the variable assignement
    usuallythat measn theres a missing or misplaced quote either " or ' meanign the compiler cannot understand the rest of the statement#
    the code breaks on the line it first hit the probelm, which often measn the fault is in the preceding line


    in this case after the replace it should read
    ",'" & replace(Description, "'", "''") & "'" & _
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you struggle to read the " or ' then consider using the appropriate character as Ascii character code
    if you are using " to encapsualte the test then its chr$(34)
    if you are using ' to encapsualte the test then its chr$(39)

    Code:
    SQLtext = " INSERT INTO tbl_Orders (fkeyPSA_ID,Item,Part_No,Description,Category,Sub_ Category,List_Price)" & _
    " VALUES (" & _
    Ref_ID  & _ 
    "," & chr$(39) & Item & chr$(39) & _ 
    "," & chr$(39) & Part_No & chr$(39) & _
    "," & chr$(39) & replace(Description, "'", "''") & chr$(39) & _ 
    "," & chr$(39)& Category & chr$(39) & _
    ",'7'" & _
    "," & List_Price & _
    ");"
    whether thats more readable I'll leave up to you.
    it does help when Intellisense kicks in and helpfully adds symbols.....
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks healdem, I'm going to have another go tomorrow. I like the idea using ASCII. thanks for that tip!!
    marcus

Posting Permissions

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