Another Insert Problem 2

[jpl458]

Got some great help here for an Insert problem. However, When I run the code on some records in the record set I get:
Run-time error '3134'
Syntax error in INSERT INTO statement
I have examined the previous record that gets inserted and compared it to the record that bombs and I see no syntax difference

The good INSERT is:
INSERT INTO BallsTable ([Account_Number], [FirstName], [LastName], [Date_of_Transaction], [Account_Type_2], [CYear], [CMonth], [Dayofwk], [WkDayNo], [FYr], [FMo], [WkDay], [WeekNum], [MoDay], [Location_Id], [StartTime], [EndTime], [Duration], [Hour], [Balls]) VALUES ('000061', ' ', ' ', #06/17/2013#, 'Paper', '2013', 'Jun', 'Mon', 1, 2014, 2, 1, 25, 17, 'DW30', #12/30/1899 18:18:00#, #12/30/1899 19:12:00#, 54, 18, 23)

The Bad INSERT is:
INSERT INTO BallsTable ([Account_Number], [FirstName], [LastName], [Date_of_Transaction], [Account_Type_2], [CYear], [CMonth], [Dayofwk], [WkDayNo], [FYr], [FMo], [WkDay], [WeekNum], [MoDay], [Location_Id], [StartTime], [EndTime], [Duration], [Hour], [Balls]) VALUES ('00006112301', , , #05/08/2013#, ' ', '2013', 'May', 'Wed', 3, 2014, 1, 3, 19, 8, 'DW08', #12/30/1899 10:07:00#, #12/30/1899 10:10:00#, 3, 9, 13)

Fields and values are equal.

The insert is built in a Helper that MajP provided that has four functions. But I see no difference between the good and the bad. How can a syntax error be data dependant?

Really stumped

jpl

 

[SkipVought]

Hi,

The second and third VALUES ought to be '','', not ,,

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jpl458]

The Problem is in the FirstName, LastName in values
The good = VALUES ('000061', ' ', ' ',
The Bad = '00006112301', , , - Its missing the quotes for a text field
The insert was created using the following code supplied by MajP and is easier to use then doing it by hand.
The problem is probably in the function that sets up the text field (The Blue Part) but I can't figure out why it happens. The records that are very few, and the rest do just fine. Thanks in advance.

jpl



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 IfEnd 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
Public Function createInsert(tableName As String, flds As String, Vals As String) As String
createInsert = "INSERT INTO " & tableName & " " & flds & " VALUES " & Vals
End Function

 

[PHV]

I'd try this function:

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886