Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

SQL Insert INTO script syntax error

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
Doi7722
Asked:
Doi7722
  • 2
  • 2
1 Solution
 
jefftwilleyCommented:
Dates:
",#" & [Forms]![Dealing Input]!ModifiedDateTime & "#,"

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

J
0
 
Doi7722Author Commented:
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
 
jefftwilleyCommented:
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
 
Doi7722Author Commented:
All sorted thanks for your help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now