Syntax error claimed by SQL?

[GraeagleBill]

Can anyone see the syntax error claimed by SQL? All fields are text except "FamilySalutation" which is integer. Both "FamilyPhone" and "FamilyE-Mail" are in fact zero-length strings.

SQL execution error: 3134 Syntax error in INSERT INTO statement.


SQL STMT: INSERT INTO Families (FamilyName,FamilyAddress,FamilyCityState,FamilyZi p,FamilySalutation,FamilyPhone,[FamilyE-Mail]) Values("Ackerman, Ron","645 J Street","Gering, NE","69341-3377",1,"","")

Thanks,
Bill

[June7]

Where are you building this sql? If SQL STMT is a constructed field in a query, cannot run an INSERT action as a subquery.

If you want to run this from VBA, the entire SQL construct needs to be within quote marks because it is a string value. Then all the string values need to be within apostrophe delimiters.

Is table set to allow empty strings - I don't allow empty strings in any fields. Use Null instead.

CurrentDb.Execute "INSERT INTO Families (FamilyName,FamilyAddress,FamilyCityState,FamilyZi p,FamilySalutation,FamilyPhone,[FamilyE-Mail]) Values('Ackerman, Ron','645 J Street','Gering, NE','69341-3377',1,Null,Null)"

[ssanfu]

In my testing, SQL is complaining about the two empty strings.
If you change
<snip>,"Gering, NE","69341-3377",1,"","") <snip>
to
<snip>,"Gering, NE","69341-3377",1," "," ") <snip> (1 space between the quotes)
I do not get the error. But that leave a single space in the fields. Not ideal.....

Then I changed the SQL string to

INSERT INTO Families (FamilyName,FamilyAddress,FamilyCityState,FamilyZip,FamilySalutation,FamilyPhone,[FamilyE-Mail]) 
Values("Ackerman, Ron","645 J Street","Gering, NE","69341-3377",1,Null,Null)

The query executed without error.

So, it seems the problem is the "" in the query...

[GraeagleBill]

The statement as posted in this thread was copied from the log file created during the running of the app. As such, it shows in the log without the quotes. They are however, included when the CurrentDb.Execute statement is run. The SQL string is constructed in code within a general module. What puzzles me the most is that the code ran fine until I changed "FamilySalutation" from text to integer. Previously, I just let SQL deal with the conversion during the table insertion.

[June7]

So what is SQL STMT:? Just a label in the forum post, nothing actually in your code?

Why did you change the data to integer? If the field is a text type, still need the value within delimiters

[GraeagleBill]

Yes, I thought perhaps using a single blank character whenever an zero-length string occurred, but that would cause some un-wanted side effects in the app. Also, I can't set string variables to NULL, so that's out as well, though perhaps I could use a temporary variable DIM'd Variant and set them to NULL when the source is found to be zero-length.

[GraeagleBill]

SQL STMT: is a label in the log file from which I copied the offending statment. I changed the type from text to integer because the table design is Number for that field.

[ssanfu]

I had the same problem in an update query in code.
I ended up modifying the code to build the update SQL on-the-fly.
If the length any field's value was zero, I omitted the field and the value from the SQL update string.

[GraeagleBill]

That's a great idea. My code builds the code statement one field at a time, so I'll simply omit the field if it's a zero-length string. I'll post back after I fix that.
Thanks,
Bill

[GraeagleBill]

Here's an example of what my error handler writes to the apps log file:

SQL execution error: 3134 Syntax error in INSERT INTO statement.
SQL STMT: INSERT INTO Families([FamilyName], [FamilyAddress], [FamilyCityState], [FamilyZip], [FamilySalutation])
Values("Ackerman, Ron","645 J Street","Gering, NE","69341-3377",1)

The statement is wrapped with quotes even though they're omitted when the error handle writes to the log. And yes, those are labels prefixed.

[GraeagleBill]

By-the-way, an item of interest here is that even though SQL is claiming a syntax error, all the insertions were done anyway, EXCEPT where one of the statements included a non-zero-length phone number.

Here's the code that builds the statement:

strInsert = "INSERT INTO Families([FamilyName], [FamilyAddress], [FamilyCityState], [FamilyZip], [FamilySalutation]"
strValues = " Values(""" & strFamilyName & """,""" & FT(1) & """,""" & strFamilyCtySt & ""","
strValues = strValues & """" & FT(4) & """," & intSalutation

If Len(FT(5)) > 0 Then
    strInsert = strInsert & ",[HomePhone]"
    strValues = strValues & ",""" & FT(5) & """"
End If

If Len(FT(7)) > 0 Then
    strInsert = strInsert & ",[e-MailAddr]"
    strValues = strValues & ",""" & FT(7) & """"
End If

strInsert = strInsert & ")"
strValues = strValues & ")"

strSQL = strInsert & " " & strValues


CurrentDb.Execute strSQL

[GraeagleBill]

My deepest apologies to those whose time was spent on this thread. The problem turned out to be related to the error handling. There are two Subs within the same general module that Insert rows into tables, the one you've seen posted repeatedly is that of the "Families". The other is the "Registry" table. (This is ALL new code being added to the app.) What was missing was the error handler in the second Sub where rows are INSERTed into the "Registry". Using a variable (strSQL) global to the module but NOT shared by the two Subs, any SQL errors in the second Sub were being serviced by the error handler in the "Families" Sub where the content of variable strSQL was being posted in the apps log. Meaning anytime there was an error with insertions into the "Registry" were being reported as though the "Families" INSERT was the offending statement. The obvious resolution was the addition of a proper error handler in the 2nd sub, after which it was easy to determine what was wrong with the INSERT statement relating to the "Registry" table.

Again, my apologies for wasting your time.

Bill