Run-time error 3134: Syntax error in INSERT INTO statement

[frogmann]

Hello, am having a terrible time with this INSERT INTO statement. I know that single quotes go around text, dates need hashes, numerics need nothing. I can see the values for each of these is correct in debug mode, but still get the syntax error. If anyone can see the problem, would be very much appreciated.
Code:
sqlRef2 = "INSERT INTO tblStudent ( RefID, LangID, StatusID, FName, MName, LName, Suffix, PID, InstutID, Address, address2, HousingNum, City, State, StateID, Zip, CountryID, Prisoner, Gender, DOB, EarliestOut, DIF, DistributionCenter ) VALUES ( " & rs1![RefID] & ", " & rs1![LangID] & ", " & rs1![StatusID] & ", '" & rs1![FName] & "', '" & rs1![MName] & "', '" & rs1![LName] & "', '" & rs1![Suffix] & "', '" & rs1![PID] & "', " & rs1![InstutID] & ", '" & rs1![Address] & "',) " & _
"('" & rs1![address2] & "', '" & rs1![HousingNum] & "', '" & rs1![City] & "', '" & rs1![State] & "', " & rs1![StateID] & ", '" & rs1![Zip] & "', " & rs1![CountryID] & ", " & rs1![Prisoner] & ", '" & rs1![Gender] & "', #" & rs1![DOB] & "#, #" & rs1![EarliestOut] & "#, #" & rs1![DIF] & "#, " & rs1![DistributionCenter] & ");"

Also, here are the fields for clarification:
RefID: Number; LangID: Number; StatusID: Number; FName: Text; MName: Text; LName: Text; Suffix: Text; PID: Text; InstutID: Number; Address: Text; address2: Text; HousingNum: Text; City: Text; State: Text; StateID: Number; Prisoner: Yes/No; Gender: Text; DOB: Date/Time; EarliestOut: Date/Time; DIF: Date/Time; DistributionCenter: Number;

Thanks so much for any time spent on this!

 

[dhookom]

You have extra ()s. Also, you may have nulls in Suffix, MName, and possibly others. Does your table accept zero length strings? Have you used Debug.Print sqlRef2 to view the entire resolved SQL statement? I generally use 2 double-quotes rather than 1 single-quotes since some last names contain a single quote which will break your code.

sqlRef2 = "INSERT INTO tblStudent ( RefID, LangID, StatusID, FName, MName, LName, Suffix, PID, InstutID, Address, address2, HousingNum, City, State, StateID, Zip, CountryID, Prisoner, Gender, DOB, EarliestOut, DIF, DistributionCenter ) VALUES ( " & rs1![RefID] & ", " & rs1![LangID] & ", " & rs1![StatusID] & ", '" & rs1![FName] & "', '" & rs1![MName] & "', '" & rs1![LName] & "', '" & rs1![Suffix] & "', '" & rs1![PID] & "', " & rs1![InstutID] & ", '" & rs1![Address] & "',[red][b])[/b][/red] " & _
 "[red][b]([/b][/red]'" & rs1![address2] & "', '" & rs1![HousingNum] & "', '" & rs1![City] & "', '" & rs1![State] & "', " & rs1![StateID] & ", '" & rs1![Zip] & "', " & rs1![CountryID] & ", " & rs1![Prisoner] & ", '" & rs1![Gender] & "', #" & rs1![DOB] & "#, #" & rs1![EarliestOut] & "#, #" & rs1![DIF] & "#, " & rs1![DistributionCenter] & ");"

Duane
Hook'D on Access
MS Access MVP

 

[frogmann]

Yes, it was the extra () that did it. Thank you! Strange, as I thought they were needed when doing line breaks. I guess just depends on context. Oh - table did accept 0-length strings. Thanks also for the tip with the doublequotes - makes sense.
Your help is very much appreciated.