Syntax/Compile Error On 'strSQL' Statement

lhooker

Registered User.
Local time
Today, 18:59
Joined
Dec 30, 2005
Messages
423
I copied the below code from a working form's 'Event' section and modified it, but I'm getting a 'Syntax' and 'Compile' error on the 'strSQL' statement. Below is snippet of the code. Also, I substituted some data into variables.
Can someone take a look at the code and let me know why I'm getting the 'SynTax' and 'Compile' error ? Thanks ! ! ! :banghead:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strBlanks As String
Dim strDesc As String
Dim strTotExp As Integer
Dim strLongDesc As String
Dim strPerCentIncr As Integer
Dim strBudgetItmIncr As Integer
Dim strOldBud As Integer
Dim strNewBudAmt As Integer
Dim strIngnre As String


strDesc = "Test Desc"
strTotExp = 1,225
strLongDesc = "Test Long Desc"
strPerCentIncr = 2
strBudgetItmIncr = 550
strOldBud = 1,270
strNewBudAmt = 3,445
strIngnre = "Yes Ignore "

DoCmd.SetWarnings True

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryInsertData")

strSQL = "INSERT INTO Budget(Description, Total_Expense, Long_Description, [Percentage Increase], [Budget Item Increase]," _
"[Old Budget], [New Budgeted Amount], Ignore)"
"VALUES (" & strDesc & ", " & strTotExp & ", " & strLongDesc & ", " & strPerCentIncr & ", " & strBudgetItmIncr & ", _
"& strOldBud & ", " & strNewBudAmt & ", " & strIngnre & ");"

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryInsertData"

Set db = Nothing
Set qdf = Nothing

DoCmd.SetWarnings False
 
..

strSQL = "INSERT INTO Budget(Description, Total_Expense, Long_Description, [Percentage Increase], [Budget Item Increase]," _
"[Old Budget], [New Budgeted Amount], Ignore)"
"VALUES (" & strDesc & ", " & strTotExp & ", " & strLongDesc & ", " & strPerCentIncr & ", " & strBudgetItmIncr & ", _
"& strOldBud & ", " & strNewBudAmt & ", " & strIngnre & ");"
First you have forgotten some & and _ or have placed them wrong, then you are missing ' for fields with string values.
strSQL = "INSERT INTO Budget(Description, Total_Expense, Long_Description, [Percentage Increase], [Budget Item Increase], " _
& "[Old Budget], [New Budgeted Amount], Ignore) " _
& "VALUES ('" & strDesc & "', " & strTotExp & ", '" & strLongDesc & "', " & strPerCentIncr & ", " & strBudgetItmIncr & ", " & strOldBud & ", " & strNewBudAmt & ", '" & strIngnre & "');"
 
JHB,

That worked . . . Thanks ! ! ! I have an additional question . . . How come I get an error when I removed the hard coded values in the variables (in the described) and use the the values that is displayed in a 'MsgBox' message just above the hard coded values in the variables. ? The values are there, but the code described fails.
 
I can't see you Msgbox in the code, but Msgbox doesn't return input message, so use a Inputbox instead.
Normally errors in SQL string is values which isn't in the right type, ex. storing text in a number field and versa visa.
 
JHB,

I found the error . . . There was a single and double quotes in a few of the variables. Again, Thanks for your help ! ! !:D
 

Users who are viewing this thread

Back
Top Bottom