Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-21
6
Medium Priority
?
205 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
Comment
Question by:pgwacd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 6

Accepted Solution

by:
PePi earned 500 total points
ID: 12371367
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
ID: 12371381
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
ID: 12373209
is the "for" column a character-type column?
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 12377230
>>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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

596 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