Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2009
    Posts
    23

    Unanswered: Run-time error '3075'

    Pls. Help...

    I have this code:
    DoCmd . RunSQL "INSERT INTO TInvMaster(InvNo,CustName) VALUES (" & MeInvNo & ",(' " & MeCustName & " '))"

    And I have this error:
    Run-time error '3075':
    Syntax error(missing operator) in query expression '('WAYNES' AUTO'))'


    Thx
    Last edited by aldwin890; 04-11-11 at 02:45.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Note the section here that discusses when the value has an apostrophe in it.

    Building SQL in VBA
    Paul

  3. #3
    Join Date
    Sep 2009
    Posts
    23
    Thnx...I tried the one with apostrophe inside but it encounter error if the "MeCustName" has no apostrophe...

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post your revised code?
    Paul

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider uysing the replace function.
    IIRC access needs to duplicate the apostrophe

    so
    Code:
    DoCmd . RunSQL "INSERT INTO TInvMaster(InvNo,CustName) VALUES (" & MeInvNo & ",(' " & replace(MeCustName, "'", "''") & " '))"
    incidentally I'd alwasy suggest you assign the SQL to a variable before using

    Code:
    strSQL = "INSERT INTO TInvMaster(InvNo,CustName) VALUES (" & MeInvNo & ",(' " & replace(MeCustName, "'", "''") & " '))"
    DoCmd . RunSQL strSQL
    ..the reason, then you cna see precisely what you are sendign to the SQL engine as opposed to wahrt you think you are sending. natch one you have proven your code there's no reason why you shlouldn't use it all in one line though.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2009
    Posts
    23
    Thank you for the replies guys...
    Healdem,ur code works very well.I'll do what you suggest.

    strSQL = "INSERT INTO TInvMaster(InvNo,CustName) VALUES (" & MeInvNo & ",(' " & replace(MeCustName, "'", "''") & " '))"
    DoCmd . RunSQL strSQL

    I always have problem with quotes,I have to work more on this area

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can reduce the pain of apostrophes or quote marks by using the ascii represntation

    chr$(34) is the quote mark:"
    chr$(39) is the apostrophe:'

    eg
    strSQL = "select my, comma, separated, column, list from my table where userid = " & chr$(39) & "aldwin890" & chr$(39)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2009
    Posts
    23
    Healdem thank you so much for your help and tips. I'm here again facing same problem with quotes...I am trying to figure out this but why is it that I can't get it correct...

    MeDscntPcnt = DLookup("DscntPct", "TCustDscnt", " CustName = (' " & Replace(MeCustName, "'", "''") & " ') AND DeptNo = ( '" & MeDeptNo & "')")

    The above code returns null even if there's a record exist.If I remove the CustName and just do the DeptNo, I can make it work...But with CustName, I have difficulty to make it right ...Thank you once again...

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you really looking for a customer name encapsualted by spaces?

    ' " & Replace(MeCustName, "'", "''") & " '
    meand
    custname = ' aldwin890 '
    NOT
    custname = 'aldwin890'

    I strongly recommend that you develop soem debugging skills.
    whenever you are developing SQL assignt he SQL to a variable and then examine that varaible to make absolutely certain that what you are sending to the SQL enginew is what you think it is.
    as to how you develop your debuggin skills, thats up to you. you can pepper your code with message boxes
    eg
    strSQL = " CustName = '" & Replace(MeCustName, "'", "''" & "') AND DeptNo = '" & MeDeptNo & "'"
    msgbox (strSQL)

    but better yet learn to use the IDE's integrated debugging tools, watches and so on. the real advantage of the IDE debugging is that you can set multipel stops, step through code, examine any variables, you can set values if required. you can examine anything
    Google

    I'd also suggest that you consider using the code suggested in post #14

    strSQL = " CustName = '" & Replace(MeCustName, "'", "''" & "') AND DeptNo = " & chr$(39) & MeDeptNo & chr$(39)
    to me it makes reading the code easier, there is less risk of screwing up the puinctuation, but thats mroe about semantics
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2009
    Posts
    23
    Actually, I'm looking for a discount of a customer. I have a table TCustDscnt and the fields are CustName,DeptNo and DscntPct. I want to get the value of DscntPct with a given CustName and DeptNo. ( The Custname can be having apostrophe or NO apostrophe...)...
    Thnx for the help and suggestions,aprreciate it.

  11. #11
    Join Date
    Sep 2009
    Posts
    23
    healdem thnx! I finally got it, the code below works great!
    I'm still familiarizing myself of using the chr$(34) and chr$(39)...I learned alot of everything that u wrote above...

    strCustName = Replace(MeCustName, "'", "''")
    strDscntPct = "SELECT TCustDscnt.CustName, TCustDscnt.DeptNo,(TCustDscnt.DscntPct) AS DscntPct FROM TCustDscnt" & _
    " WHERE ((TCustDscnt.CustName) = '" & strCustName & "' AND (TCustDscnt.DeptNo) = '" & MeDeptNo & "')"

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is deptno a text column or a numeric column?
    text columns in a where clause should be quoted
    eg where customername = "smiff"
    where custmername line "smif*"

    numbers should be unquoted
    eg
    where customernumber = 5
    where customernumber <=55
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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