?
Solved

SQL Insert INTO script syntax error

Posted on 2010-01-12
4
Medium Priority
?
367 Views
Last Modified: 2013-11-28
I have an SQL script that I keep getting a syntax error when I try and run. Can anyone see an obvious problems.
mySQL = ("INSERT INTO [SHARE REGISTER] (CompanyID, InvestorID, CompanyName, " & _
            "InvestorName, BidPrice, OfferPrice, NoShares, " & _
            "Agent, AgentFee, RedemptionCharge, TransactionType, " & _
            "Consideration, ReceivedDate, ReceivedTime, ReceivedBy, " & _
            "CDDComplete, CashUnit, AgentFeeAmount, NetConsideration, Notes, TransactionDate, " & _
            "ModifiedBy, ModifiedDateTime, VerifiedBy, VerifiedDateTime, TransactionID) " & _
            "VALUES " & [Forms]![Dealing Input]!CompanyID & "," & [Forms]![Dealing Input]!InvestorID & "," & _
            "" & [Forms]![Dealing Input]!FundCombo & "," & [Forms]![Dealing Input]!InvestorName & "," & _
            "" & [Forms]![Dealing Input]!BidPrice & "," & [Forms]![Dealing Input]!OfferPrice & "," & _
            "" & [Forms]![Dealing Input]!NoShares & "," & [Forms]![Dealing Input]!Agent & "," & _
            "" & [Forms]![Dealing Input]!AgentFee & "," & [Forms]![Dealing Input]!RedemptionCharge & "," & _
            "" & [Forms]![Dealing Input]!TransactionType & "," & [Forms]![Dealing Input]!Consideration & "," & _
            "" & [Forms]![Dealing Input]!ReceivedBy & "," & [Forms]![Dealing Input]!ReceivedDate & "," & _
            "" & [Forms]![Dealing Input]!ReceivedTime & "," & [Forms]![Dealing Input]!CDDComplete & "," & _
            "" & [Forms]![Dealing Input]!WorldCheck & "," & [Forms]![Dealing Input]!CashUnit & "," & _
            "" & [Forms]![Dealing Input]!AgentFeeAmount & "," & [Forms]![Dealing Input]!NetConsideration & "," & _
            "" & [Forms]![Dealing Input]!Notes & "," & [Forms]![Dealing Input]!TransactionDate & "," & _
            "" & [Forms]![Dealing Input]!UserID & "," & [Forms]![Dealing Input]!ModifiedDateTime & "," & _
            "" & [Forms]![Dealing Input]!RecordID & "")

Open in new window

0
Comment
Question by:Doi7722
[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
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 26293024
Dates:
",#" & [Forms]![Dealing Input]!ModifiedDateTime & "#,"

Text
"'" & [Forms]![Dealing Input]!Notes & "',"

J
0
 

Author Comment

by:Doi7722
ID: 26293237
J
 
I have updated all the fields now but am still getting the error. I have shortened the code and still get the error. Attached is the full code.

Dim mySQL As String

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection


Dim RecordID As Integer
Dim ctl As Control
    If IsNull(Me.RecordID) Or Me.RecordID = Empty Or Me.RecordID = "" Then
    DIR_DealRef_Generator
    RecordID = Forms![Dealing Input]!RecordID
    Else
    DoCmd.SetWarnings False
    RecordID = Forms![Dealing Input]!RecordID
    DoCmd.RunSQL ("INSERT INTO [SHARE REGISTER_A] (ID, CompanyID, CompanyName, InvestorID, InvestorName, BidPrice, OfferPrice, NoShares, Agent, AgentFee, RedemptionCharge, TransactionType, Consideration, ReceivedDate, ReceivedTime, ReceivedBy, CDDComplete, WorldCheck, Nominal_Ledger_Code_DR, Nominal_Ledger_Code_CR, CashUnit, AgentFeeAmount, NetConsideration, Notes, TransactionDate, ModifiedBy, ModifiedDateTime, VerifiedBy, VerifiedDateTime, TransactionID) Select ID, CompanyID, CompanyName, InvestorID, InvestorName, BidPrice, OfferPrice, NoShares, Agent, AgentFee, RedemptionCharge, TransactionType, Consideration, ReceivedDate, ReceivedTime, ReceivedBy, CDDComplete, WorldCheck, Nominal_Ledger_Code_DR, Nominal_Ledger_Code_CR, CashUnit, AgentFeeAmount, NetConsideration, Notes, TransactionDate, ModifiedBy, ModifiedDateTime, VerifiedBy, VerifiedDateTime, TransactionID From [SHARE REGISTER] WHERE TransactionID = RecordID")
    DoCmd.RunSQL ("DELETE From [SHARE REGISTER] WHERE TransactionID = RecordID")

    DoCmd.SetWarnings True
    End If
     
  mySQL = ("INSERT INTO [SHARE REGISTER] (CompanyID, InvestorID, CompanyName) " & _
            "VALUES '" & [Forms]![Dealing Input]!CompanyID & "', '" & [Forms]![Dealing Input]!InvestorID & "', '" & [Forms]![Dealing Input]!FundCombo & "'")
     

            MsgBox (mySQL)
     cn.Execute mySQL

Open in new window

0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
ID: 26294548
mySQL = ("INSERT INTO [SHARE REGISTER] (CompanyID, InvestorID, CompanyName) " & _
            "VALUES ('" & [Forms]![Dealing Input]!CompanyID & "', '" & [Forms]![Dealing Input]!InvestorID & "', '" & [Forms]![Dealing Input]!FundCombo & "'")

You were missing an opening parenthesis

 Here's a working syntax

        sSql = "Insert into " & eTable & " (log_date, app, msg, type, lvl)"
        sSql = sSql & " Values (#" & Now & "#, '" & sApp & "', '" & sMsg & "','" & sType & "', " & iLVL & ");"

J
0
 

Author Closing Comment

by:Doi7722
ID: 31676082
All sorted thanks for your help.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

770 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