Syntax error in INSERT INTO statement.

[John Nelson]

I'm having trouble with the INSERT INTO statement. I can't figure out why I'm getting a syntax error. Any ideas?

strSQL = "INSERT INTO tblTransactions ( PartID, ProjectID, TransDate, Qty, TotalPrice ) "
strSQL = strSQL & "SELECT" & intPartID & ", " & intProjID & ", " & datDate & ", "
strSQL = strSQL & intQty & ", " & varTotPrice
    cnn.Execute strSQL

The debugger is grabbing the cnn.Execute strSQL line.


Here's the whole procedure in case it's someting other than the SQL stuff.

Thanks!

Private Sub cmdEnterTrans_Click()

Dim intPartID As Integer, intQty As Integer, intProjID As Integer
Dim datDate As Date, varPrice As Variant, varTotPrice As Variant
Dim strSQL As String
Dim cnn As ADODB.Connection, rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
cnn.BeginTrans

If IsNull(Me!PARTNO) Then
    MsgBox "Please enter a part number before continuing."
    Me!PARTNO.SetFocus
    Exit Sub
End If

If IsNull(Me!txtQty) Or IsNull(Me!txtDate) Then
    MsgBox "Please fill out the transaction form completely before continuing."
    Me!Qty.SetFocus
    Exit Sub
End If
    

intPartID = Me!PartID
intProjID = Me!cboProj.Column(1)
datDate = Me!txtDate
intQty = Me!txtQty
varPrice = DLookup("PRICE", "tblParts", [PartID] = "intPartID")
varTotPrice = intQty * varPrice
    
strSQL = "INSERT INTO tblTransactions ( PartID, ProjectID, TransDate, Qty, TotalPrice ) "
strSQL = strSQL & "SELECT" & intPartID & ", " & intProjID & ", " & datDate & ", "
strSQL = strSQL & intQty & ", " & varTotPrice
    cnn.Execute strSQL


cnn.CommitTrans


End Sub

 

[modest]

1a) dont use SELECT... use VALUES ( )

1b) unless you're doing an insert based off a SELECT... in which case you'll need a FROM clause.

2) not to mention your "SELECT" needs a space between it and the variable... so do "SELECT "

3)plus you need to add a semi-colon at the end so you need

 & ";"

 

[RV]

Your use of Execute is incorrect.
It should read dbs.Execute, dbs representing the databse you want to execute upon.
You need to define dbs as a variable

Dim dsb As ADODB.Databse

and set it.

Or use CurrentDb.Execute if the database is your current active one.

RV

 

[RoyVidar]

The use of the execute method of an ADO connection is correct.

The ADO "equivalence" of the DAO databaseobject, may perhaps be the connection object, though there are some differences, but they share the .Execute method, which is somewhat similar. One doesn't need the semicolon at the end of vba sql strings.

The likely problem here, is the lack of datatype delimiters.

From the description, it seems only the date needs delimiters. The select vs values, I use the latter for dynamic operations:

strSQL = "INSERT INTO tblTransactions ( PartID, ProjectID, TransDate, Qty, TotalPrice ) "
strSQL = strSQL & "Values(" & intPartID & ", " & intProjID & ", #" & datDate & "#, "
strSQL = strSQL & intQty & ", " & varTotPrice &")"

If you're living somewhere where the date settings differ from US, you may need to format the date to an unambiguous format, for instance:

", #" & format$(datDate,"yyyy-mm-dd") & "#, "

Further, if your numeric settings differ from US (using comma in stead of dot as decimal separator), you may have to format the numbers too, at least those that can contain decimals.

" & replace(cstr(varTotPrice),",",".") &")"

Should some of the fields, in spite of being assigned integer values, be text fields, they would need single quotes as delimiters.

 

[John Nelson]

Hey thanks a lot. I'm going to give that a try. In case you guys are still watchin' this thread... What's the best data type to use for numbers with decimals? I used variant just because I didn't want to have to deal with what could happen if I use the wrong one.

Thanks!

John

 

[RoyVidar]

Usually, I'd match the datatypes of the table. For numerics defined as single, I'd use single, for numerics defined as double, I'd usd double, for fields defined as currency, I'd use currency.