Run Time error '3134' Syntax error in INSERT INTO statement

[RickD1]

Hi,

Hope someone can help, I am completely stuck right now. I have a vba script that pulls data from a query base on certain criteria. I am then using the DSum function to add the values in a particular field. I am using the same script to with the DCount function, and it works fine. See sample code below.

The funny thing is, the data is inserted into the table, but the insert error still appears, and the script stops at the first "CurrentDb......".

NB: Although not important, the task is being executed using a form.

Option Compare Database


Private Sub cmdClose_Click()
DoCmd.Close


End Sub


Private Sub cmdGenerate_Click()
Dim DateVal
Dim Monday
Dim period
Dim str
Dim Name
Dim XCD
Dim USD




DateVal = EndDate.Value






If Frame13 = 1 Then
period = " [Date]=#" & DateVal & "#"


ElseIf Frame13 = 2 Then
'check for friday
If Weekday(DateVal) = vbFriday Then
Monday = DateAdd("d", -4, DateVal)
period = " [Date] between #" & Monday & "# and #" & DateVal & "#"
Else
MsgBox "Please select a Fridays' Date", , "End of Week Error"
Exit Sub
End If


ElseIf Frame13 = 3 Then


period = " [Date] between #" & DateSerial(Year(DateVal), Month(DateVal), 1) & _
"# and #" & DateSerial(Year(DateVal), Month(DateVal) + 1, 0) & "#"
End If
DateStore.Value = period
'MsgBox period
LabelMsg.Caption = "Reporting for " & period


str = "delete * from ABC_On_NBA_Amt"
CurrentDb.Execute str


For i = 4 To 10


'Name = DLookup("AB_Name", "AB_Names_Nos", "[AB_no]=" & i)

If i < 10 Then
XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
"' and [Currency_Code]= 951 " & " and " & period)

USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
"' and [Currency_Code]= 840 " & " and " & period)

Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ0000" & i & "'")

Else
XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]= 951 " & " and " & period)

USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]= 840 " & " and " & period)

Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ000" & i & "'")

End If

The data is calculated and inserted into the table, the error still appears.

CurrentDb.Execute "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
& XCD & ",' " & Name & " '," & USD & ")"


Below does not execute because of the insert syntax error.



Next


For i = 15 To 19


XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]=951 " & " and " & period)

USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
"' and [Currency_Code]=840 " & " and " & period)

Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ000" & i & "'")




CurrentDb.Execute "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
& XCD & ",'" & Name & "'," & USD & ")"





Next


If Frame13 = 1 Then
DoCmd.OpenReport "NBA_AB_Transactions_by_DEF_Numbers", acViewPreview


ElseIf Frame13 = 2 Then
DoCmd.OpenReport "ABC_AB_Transactions_By_DEF_Weekly", acViewPreview
ElseIf Frame13 = 3 Then
'DoCmd.OpenReport "ABC_AB_Transactions_By__Monthly", acViewPreview
End If


'Dim str As String
'str = "select * from ABC_CCC_AB_Trans where locationID in string(,,)"
End Sub




Private Sub Form_Unload(Cancel As Integer)
'Forms!Switchboard.Visible = True


End Sub

 

[Joe4]

Welcome to the Board!

I would recommend stored you INSERT INTO statement in a string variable. And use a Message Box (before your CurrentDb.Execute statement) to see what you are building to see if it looks like valid SQL syntax, i.e.

Dim strSQL as String

strSQL=[COLOR=#333333]"Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _[/COLOR]
[COLOR=#333333]& XCD & ",' " & Name & " '," & USD & ")"

MsgBox strSQL
[/COLOR][COLOR=#333333]
CurrentDb.Execute strSQL[/COLOR]

 

[Micron]

Additional suggestions/points:
I hope you noticed Joe4 declared the variable as a string, whereas you did not declare the type for any of your variables - not good form. Your variables are all of the variant data type which not only uses much more memory in comparision, you are also forcing Access to determine the type and handle correctly. This does not always happen. He also added missing spaces between words.

You should ALWAYS declare Option Explicit and declare types properly; i.e.
Dim strSql as String
Dim bolOption as Boolean or if listing multiple variables on one line,
Dim strSql as String, strDept as String
not
Dim strSql, strDept as String.

You should review a list of reserved or problem words that you should avoid (Name is one of them). If you establish a uniform naming convention for yourself, you can prevent virtually all instances of this occurring. If I really want to use Name, in a procedure it would be strName. In a field, it would be Fname; Lname for example.
Microsoft Access tips: Problem names and reserved words in Access

When posting, please use code tags an reduce the empty space. It makes it harder for us to evaluate, or maybe easy to pass on reading at all. Typical indentation would be nice too.

str = "delete * from ABC_On_NBA_Amt"
CurrentDb.Execute str

I first presumed this is where you refer to the failure occurring because your OP said
the script stops at the first "CurrentDb......".. Many will ID the offending line by adding a comment on that line, otherwise we can waste your time by concentrating too much on your initial post comments and get it wrong. I'll be honest, I missed it because I was put off by the code format. Just getting old and cranky, I guess!

str = "delete * from ABC_On_NBA_Amt"
CurrentDb.Execute str 'Error number 1234 and message generated here.

For i = 4 To 10
 'Name = DLookup("AB_Name", "AB_Names_Nos", "[AB_no]=" & i)
 If i < 10 Then
   XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
   "' and [Currency_Code]= 951 " & " and " & period)
   USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
   "' and [Currency_Code]= 840 " & " and " & period)
   Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ0000" & i & "'")
 Else
   XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
   "' and [Currency_Code]= 951 " & " and " & period)
   USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
   "' and [Currency_Code]= 840 " & " and " & period)
   Name = DLookup("[AB_Name]", "[AB_Names_Nos]", "[AB_no]='XYZ000" & i & "'")
End If

 

[RickD1]

Micron,


Thank you for your points, they have been well taken, and I thank you for your time kind sir. I did review the reserved words, as I first thought it could have been my issue. This code was passed on, which I have modified to meet my needs, and I did notice the reserved word "name" and changed it, but after it didn't resolve the issue, and I didn't see any issues, I ended up putting it back. You may have noticed that I am a novice when it comes to coding, and learning as I go along. Your points will definitely go along way with helping me develop these skills.


Joe4


Your suggestion to use the message box helped greatly, and I thank you very much. I was able to see what was being inserted, and realized the issue. I will try to explain it to the best of my ability.




The code does a loop through my query looking for a particular criteria, namely the terminal name (XYZ000 and a number from 4 to 10 and also from 15 to 19); however, there are currently no entries in the query for 8, 9, 11, 12, etc, so there is nothing to insert, and it is because of this, the loop basically stops and throws the error. Below is a sample of the message that is returned when it tries to find a terminal that doesn't exist.

Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD)
values(#11/30/2015#,,",)

I know that Microsoft doesn't like when you don't have values for the specified table fields, and this is what caused the error. As I mentioned before, I have a similar code running with the DCount function, and that works fine; however, I have noticed that when the loop reaches to a terminal that doesn't exist, it passes zeros for the values and continues, but DSum doesn't. I have since resolved the issue with a workaround that I am not too proud of, as the code isn't too clean, but it's getting the job done until I can figure out a way to have the loop ignore the missing terminals, and continue. If you have any suggestions on how I may achieve this, it will be most welcomed. See modified / working code below.

Option Compare Database


Private Sub cmdClose_Click()
DoCmd.Close


End Sub


Private Sub cmdGenerate_Click()
Dim DateVal As Date
Dim Monday As String
Dim period As Variant
Dim strSQL As String
Dim DeviceName As String
Dim XCD As Currency
Dim USD As Currency


DateVal = EndDate.Value


If Frame13 = 1 Then
period = " [Date]=#" & DateVal & "#"


ElseIf Frame13 = 2 Then
'check for friday
    If Weekday(DateVal) = vbFriday Then
    Monday = DateAdd("d", -4, DateVal)
        period = " [Date] between #" & Monday & "# and #" & DateVal & "#"
    Else
        MsgBox "Please select a Fridays' Date", , "End of Week Error"
        Exit Sub
    End If


ElseIf Frame13 = 3 Then


period = " [Date] between #" & DateSerial(Year(DateVal), Month(DateVal), 1) & _
"# and #" & DateSerial(Year(DateVal), Month(DateVal) + 1, 0) & "#"
End If
DateStore.Value = period
'MsgBox period
LabelMsg.Caption = "Reporting for " & period


strSQL = "delete  * from ABC_On_DEF_Amt"
CurrentDb.Execute strSQL


  For i = 4 To 7
  
      
    ' If i < 8 Then
    
        XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
        "' and [Currency_Code]= 951 " & " and " & period)
        
        USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ0000" & i & _
        "' and [Currency_Code]= 840 " & " and " & period)
                      
        DeviceName = DLookup("[ATM_Description]", "[AB_ATM_Names]", "[ATM_no]='XYZ0000" & i & "'")
      
       
   ' End If
    
         
        strSQL = "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
  & XCD & ",'" & DeviceName & "'," & USD & ")"
  
     CurrentDb.Execute strSQL
     
 Next
 


For i = 10 To 10




    XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
    "' and [Currency_Code]=951 " & " and " & period)
    
    USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
    "' and [Currency_Code]=840 " & " and " & period)
    
     DeviceName = DLookup("[ATM_Description]", "[AB_ATM_Names]", "[ATM_no]='XYZ000" & i & "'")
     


  strSQL = "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
  & XCD & ",'" & DeviceName & "'," & USD & ")"


CurrentDb.Execute strSQL


Next


For i = 15 To 15


    XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
    "' and [Currency_Code]=951 " & " and " & period)
    
    USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
    "' and [Currency_Code]=840 " & " and " & period)
    
    DeviceName = DLookup("[ATM_Description]", "[AB_ATM_Names]", "[ATM_no]='XYZ000" & i & "'")
     


  strSQL = "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
  & XCD & ",'" & DeviceName & "'," & USD & ")"


CurrentDb.Execute strSQL


Next


'If Frame13 = 1 Then
'DoCmd.OpenReport "ABC_ATM_Transactions_by_DEF_Numbers", acViewPreview


'ElseIf Frame13 = 2 Then
'DoCmd.OpenReport "ABC_ATM_Transactions_By_DEF_Weekly", acViewPreview
'ElseIf Frame13 = 3 Then
'DoCmd.OpenReport "ABC_ATM_Transactions_By_DEF_Monthly", acViewPreview
'End If


End Sub

 

[RickD1]

Made a small modification again. I removed the extra "For" statements, as they were not needed. Example below.

i = 10 


    XCD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
    "' and [Currency_Code]=951 " & " and " & period)
    
    USD = DSum("[Amt Processed]", "[qry_ABC_On_DEF_Trans]", "[Terminal]='XYZ000" & i & _
    "' and [Currency_Code]=840 " & " and " & period)
    
    DeviceName = DLookup("[ATM_Description]", "[AB_ATM_Names]", "[ATM_no]='XYZ000" & i & "'")
     
  strSQL = "Insert into ABC_On_DEF_Amt(TransDate,TotalAmtXCD,ATMLocation,TotalAmtUSD) values(#" & DateVal & "#," _
  & XCD & ",'" & DeviceName & "'," & USD & ")"
MsgBox strSQL


CurrentDb.Execute strSQL

 

[Micron]

I did not think the names I noticed were the cause, was just making suggestions for going forward. As for any ideas on how to approach such a problem:

Suggest you try creating a recordset from a query as you loop through the values for i. If it returns no records because the needed data is missing, skip the db.execute part.
Or create a recordset from a query and check rs.fields(name or zero based column number here) to see if it's null or empty. If so, skip the db.execute.
Or look for a key value with DLookup and if not found,...
I admit I haven't deeply scrutinized your code to try to follow what's going on.