Solved

Why do I get an Syntaax error for insert into Statement .

Posted on 2004-10-21
197 Views
Last Modified: 2010-05-02
 
I am using VB and MS access... If I run the query below without inserting the value for teh FOR column everything is fine..But once i include the FOR colum and  insert the strFor it throws an error.

Now the Delivery Date which is a string is also a cell above teh FOR in teh spreadsheet   . It is represented as strDDate. That is inserting fine. Why this problem only for this  For cell. I checked almost everything.

When I run this query in immediate window and cut and paste the same query in MS access it runs fine again.. Can you please help me ....
I also checked inserting every value ..It has nothing to do with the date or anything. there is only problenm in the for.
Also in MS access backend i made the field as memo , text . Still the problem


Dim strFor as String
strFor = Trim(App.Cells(ireqRow + 1, 3))
strDDate = Trim(App.Cells(ipodRow + 1, 3))

 ssql = "insert into PURCHASE(PORDER,PODATE,SHIP_FROM,SHIP_TO,DDATE,ALL_SHIP_FOB_DEST,SHIP_VIA,TERMS,REQUESTOR, BUYER,FOR,QUOTE_NO,REQUISITION_NO,SUB_TOTAL,SHIP_HAND,SALES_TAX,DISCOUNT, DELIVERY_INSTALL,TOTAL) VALUES('"
    ssql = ssql & Replace$(strPOrder, "'", "''") & "',#" & Format(dPODate, "dd-MMM-yy") & "#,"
    ssql = ssql & lFromId & "," & lToId & ",'" & Replace$(strDDate, "'", "''") & "','" & Replace$(strASFD, "'", "''") & "','"
    ssql = ssql & Replace$(strShipVia, "'", "''") & "','" & Replace$(strTerms, "'", "''") & "','" & Replace$(strRequestor, "'", "''")   & "','"
    ssql = ssql & Replace$(strBuyer, "'", "''") & "','" & Replace$(strFor, "'", "''") & "','" & Replace$(strQuoteNo, "'", "''") & "','" & Replace$(strReqNo, "'", "''") & "',"
    ssql = ssql & dblSubTotal & "," & dblShipAndHand & "," & dblSalesTax & "," & dblDiscount & ","
    ssql = ssql & dblDelAndInstall & "," & dblTotal & ")"

' This is what teh sql statement loosk in immediate window'. I have run this is access and it inserts fine.but from teh front end when i insert
i get the error of Syntax error of insert into statement ...'Conference rooms' corresponding to FOR is teh problem...
I am reading data from various excel seets and outting it into MS access ..eveything throws an error when inserting teh FOR value

insert into PURCHASE(PORDER,PODATE,SHIP_FROM,SHIP_TO,DDATE,ALL_SHIP_FOB_DEST,SHIP_VIA,TERMS, REQUESTOR,BUYER,FOR,QUOTE_NO,REQUISITION_NO,SUB_TOTAL,SHIP_HAND,SALES_TAX, DISCOUNT,DELIVERY_INSTALL,TOTAL) VALUES('7103',#16-Jan-04#,101,202,'ASAP','Prepay & Add', 'Next Day','Net 30','Michael Lewis','JK','Conference Rooms','134278059','7710',6927.8,60,419.27,0,0,7407.07)
0
Question by:pgwacd
    4 Comments
     
    LVL 6

    Accepted Solution

    by:
    try enclosing FOR with brackets []. it might be a reserved word

    like:

    ssql = "insert into PURCHASE(PORDER,PODATE,SHIP_FROM,SHIP_TO,DDATE,ALL_SHIP_FOB_DEST,SHIP_VIA,TERMS,REQUESTOR,BUYER,[FOR],QUOTE_NO,REQUISITION_NO,SUB_TOTAL,SHIP_HAND,SALES_TAX,DISCOUNT,DELIVERY_INSTALL,TOTAL) VALUES('"....

    0
     

    Expert Comment

    by:kavgp
    Have you tried inserting the strFor, without using Trim function, since as far as i get it, you are replacing "'" with space anyway, try inserting it without using the Trim statement.
    0
     
    LVL 5

    Expert Comment

    by:gary_j
    is the "for" column a character-type column?
    0
     
    LVL 48

    Expert Comment

    by:Ryan Chong
    >>strFor = Trim(App.Cells(ireqRow + 1, 3))
    will error if App.Cells(ireqRow + 1, 3) contains null value, but seems it's from Excel, so if you like for 'safety' you can try like:
    strFor = Trim("" & App.Cells(ireqRow + 1, 3))

    btw, PePi got a nice catch there, and make sure all your fields data type are correct, as well as the sql statement
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Lean Six Sigma Project Manager Certification

    There are many schools of thought around successful project management, but few as highly regarded as the Six Sigma and Lean methods. With 37 hours of learning, this training will explain concrete processes for increasing efficiency and limiting wasted time and effort.

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now