Problem with INSERT INTO SQL 1

[pjammer1]

I have some experience with INSERT INTO statements, but when having to use them with an execute statement like conn.execute with all the quotes and all, I cannot seem to get it right.
I have a simple INSERT INTO as a tester that works fine using conn.execute strSQL. However, the first parameter needs to be an integer value instead of string and I feel like I am either going to go blind or crazy trying to figure out the correct syntax with the correct placing of the integer parameter.
(The brackets are used for YEAR and WEEK because one or maybe both of them are reserved words). Help on this would be so much appreciated.

strSQL = "INSERT INTO TEST ([YEAR], [WEEK]) VALUES ('" & intYear & "' , '" & strSeason & "')"

 

[dhookom]

strSQL = "INSERT INTO TEST ([YEAR], [WEEK]) VALUES (" & intYear & " , '" & strSeason & "')"

Duane
Hook'D on Access
MS Access MVP

 

[pjammer1]

Thanks Duane. Very much appreciated. Know I can see the pattern for future reference and of course the code works now!!

 

[Andrzejek]

Also, help yourself by:

strSQL = "INSERT INTO TEST ([YEAR], [WEEK]) VALUES ('" & intYear & "' , '" & strSeason & "')"
[red]
Debug.Print strSQL[/red]

And you can see in the Immiediate Window your SQL and your mistakes. It is a nice way to learn all those little ' and "

Have fun.

---- Andy

 

[MajP]

If you are doing a lot of insert queries these functions will save you lots of time, and never worry about the wrong syntax.

Public Sub Test()
  Dim flds As String
  Dim vals As String
  Dim strSql As String
  flds = insertFields("FirstName", "LastName", "OrderID", "OrderDate")
  vals = insertValues(sqlTxt("Mike"), sqlTxt("O'Brien"), Null, SQLDate(Date))
  
  strSql = "INSERT INTO tableName " & flds & " VALUES " & vals
  Debug.Print strSql
End Sub

Public Function insertFields(ParamArray varfields() As Variant) As String
  Dim fld As Variant
  For Each fld In varfields
    If insertFields = "" Then
      insertFields = "([" & fld & "]"
    Else
      insertFields = insertFields & ", [" & fld & "]"
    End If
  Next fld
  If Not insertFields = "" Then
    insertFields = insertFields & ")"
  End If
End Function

Public Function insertValues(ParamArray varValues() As Variant) As String
  Dim varValue As Variant
  For Each varValue In varValues
    If IsNull(varValue) Then varValue = "NULL"
    If insertValues = "" Then
      insertValues = "(" & varValue
    Else
      insertValues = insertValues & ", " & varValue
    End If
  Next varValue
  If Not insertValues = "" Then
    insertValues = insertValues & ")"
  End If
End Function

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function

Function SQLDate(varDate As Variant) As Variant
     If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function