Syntax Error driving me crazy

[SteveJtoo]

PLEASE - I need another pair of eyes and a new brain to look at this code and tell me why I am getting a "Syntax error in INSERT INTO statement'

Here is the code:
Dim strSQL As String

If (Nz(Me.Cover, 0) = 0 Or Nz(Me.Rating, 0) = 0) Then ' If the cover or rating are blank then don't put them in books
strSQL = ""
strSQL = strSQL & " INSERT INTO Books "
strSQL = strSQL & " ( Title, Author, CopyrightYear, Series, EditionNumber, Notes, Amazon )"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & Me.Title & "', " & Me.AuthorID & " , " & Me.YearPub & ", '" & Me.Series & "' , " & Me.Edition & ","
strSQL = strSQL & " '" & Me.Notes & "', '" & Me.amazon & "');"
Else ' If the cover and rating have info then put them in books
strSQL = ""
strSQL = strSQL & " INSERT INTO Books "
strSQL = strSQL & " ( Title, Author, CopyrightYear, Series, EditionNumber, Field2, Field222, Notes, Amazon )"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & Me.Title & "', " & Me.AuthorID & " , " & Me.YearPub & ", '" & Me.Series & "', "
strSQL = strSQL & " " & Me.Edition & ",'" & Me.Cover & "', '" & Me.Rating & "','" & Me.Notes & "', '" & Me.amazon & "');"
End If

Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError
**********************************************************
This is what I get from the Debug.Print strSQL:

INSERT INTO Books ( Title, Author, CopyrightYear, Series, EditionNumber, Field2, Field222, Notes, Amazon ) Values ('The Venitian Judgement', 56 , 2010, ' ', ,'C:\Documents and Settings\SWJohnson\My Documents\Books\Stone, David\TheVenitianJudgement.jpg', 'C:\Documents and Settings\SWJohnson\My Documents\Books\Buttons\Stars\stars-4-0.gif','', '#http://www.amazon.com/The-Venetian-Judgment-David-Stone/dp/B002KAOS0G#');

Looks right to me but . . .

Thanks for any help.

 

[pr2-eugin]

You have 9 FIELDS SET to insert, but not the same amount of VALUES.. Check that.. Also make sure the TYPES of them are right..

 

[nanscombe]

INSERT INTO Books ( Title, Author, CopyrightYear, Series, EditionNumber, Field2, Field222, Notes, Amazon ) Values ('The Venitian Judgement', 56 , 2010, ' '>>, ,<<'C:\Documents and Settings\SWJohnson\My Documents\Books\Stone, David\TheVenitianJudgement.jpg', 'C:\Documents and Settings\SWJohnson\My Documents\Books\Buttons\Stars\stars-4-0.gif','', '#http://www.amazon.com/The-Venetian-Judgment-David-Stone/dp/B002KAOS0G#');

Looks right to me but . . .

Thanks for any help.

It appears to be a problem with a blank (NULL) edition number. I assumed it to be a number.

If I put a 0, it attempts to insert a record. If I put a blank field '' it attempts to insert a record. If I leave it empty it gives the SQL error.

I hope that gives you a clue.

I copied and pasted your SQL into a query, in SQL view mode, and after failing it moved the cursor to just after the blank field in the SQL.

 

[SteveJtoo]

Thank You Nigel. You are exactly right!!! Whew!