Syntax Error VBA SQL Insert Into query. Access 2007 and SQL Server 2008

[Phred]

I am getting a syntax error on the following query. When I run debug print it shows all the correct data. But when I execute it I get a Syntax Error. Doesn't seem to be anything related to data type or variables. Can anyone see something I can't. Thanks.

stSQL = "INSERT INTO DBO_PropertyYearDetail" & _
    "( TaxYear, PropertyID, County, TownshipNM, kPIN, CycleID, CycleName, LevelofAssess, Age, PctOwnOccupied, TennantName, LastPurchDT, LastPurchPrice, TotalLandSF, TotalGrossBldgSF, TotalNetBldgSF, NoOfBldgs, NoOfUnits, TaxRate, FileNo, kAttyAssigned, FirstYRTriFeeP3, FirstYRTriFeeP4, SecondYrTriFeeP3, SecondYrTriFeeP4, ThirdYrTriFee, VOPTABFeeP5, CEYEAR, TaxCode) " _
    & "VALUES ('" & stNewTaxYr & "'," & lngPropID & ",'" & stCounty & "','" & stTownship & "','" & stKPIN & "','" & stCycleID _
    & "','" & stCycleNM & "'," & noLOA & "," & lngNewAge & "," & noPctOwnOcc & ",'" & stTennantNM & "','" & stPurchDT & "'," _
    & lngPurchPrice & "," & lngLandSF & "," & lngGrossSF & "," & lngNetSF & "," & lngNoBldgs & "," & lngUnits & "," & noTaxRate _
    & ",'" & stFileNo & "','" & stKATTY & "'," & varFYTFP3 & "," & varFYTFP4 & "," & varSYTFP3 & "," & varSYTFP4 & "," & varTYTF _
    & "," & varVPFP5 & ",'" & varCEYEAR & "','" & stTaxCode & "');"
    Debug.Print stSQL
    DoCmd.RunSQL stSQL

Phred

[CJ_London]

the stSQL seems to be created OK so perhaps you have coded for a text value when it is actually a numeric or visa versa, or perhaps a value is null - what is the full syntax error?

[pbaldy]

Copy the SQL from the debug into a new query in SQL view and try to run it. You may get a better error message.

[ssanfu]

Have to agree with Ajax about the SQL. Does the error happen on the first insert or later?

What does the debug statement look like when it errors?

[Phred]

I don't get a data type mismatch and the only fields required to have data are the PK and the FK of the table. Everything else allows nulls. Full error is Run-Time Error '3134 (really vague); Syntax error in Insert Into statement. Here are the actual debug.print statement. All the values look correct to me.

INSERT INTO DBO_PropertyYearDetail( TaxYear, PropertyID, County, TownshipNM, kPIN, CycleID, CycleName, LevelofAssess, Age, PctOwnOccupied, TennantName, LastPurchDT, LastPurchPrice, TotalLandSF, TotalGrossBldgSF, TotalNetBldgSF, NoOfBldgs, NoOfUnits, TaxRate, FileNo, kAttyAssigned, FirstYRTriFeeP3, FirstYRTriFeeP4, SecondYrTriFeeP3, SecondYrTriFeeP4, ThirdYrTriFee, VOPTABFeeP5, CEYEAR, TaxCode)
VALUES ('2015',25,'Cook','Lake View','14181170210000','2','Chicago',0.1,112,0,'', '',0,4770,3158,0,1,3,6.396,'3','Mmactal ',0.25,0.25,0.25,0.25,0.25,,'','73001');

If you're not seeing an error I suspect it must be something obscure in my Dim variable AS...

I really appreciate your help.

Phred

[Phred]

I get the error on the first insert. I will try Baldy's suggestion shortly.

[Phred]

I put it into the query builder, got rid of the syntax Access doesn't like. The fields show up in the Append To fields. When I try to run it now I get a "Data type mismatch in criteria expression. That's interesting that it would show up here. No a hint as to the field though.

Phred

[pbaldy]

The third value from the end is empty.

[ssanfu]

You can also start out with 7 fields and see if the SQL errors. If it doesn't error, add more fields. Keep adding fields until you get an error. Then you will know which field is causing the error.

[Phred]

I gotta get better glasses, you're right. I entered the value in the source form field and then it executed fine. Question, if the destination field isn't required (it can be null) why would it error out? Shouldn't it just not insert the data?

Thanks to all who chipped in. Much appreciated.

Cheers for all.

[Phred]

SSANFU that's a real good idea too. I learned a lot on this one. I shoulda thought of that one. Check the simple stuff first.

[pbaldy]

I tend to use 2 variables, and only the field and value when the form control has a value.

[Phred]

Paul I don't understand what your trying to say in your previous comment on the 2 variables. How would that help?

The field on the form was empty. It shouldn't have been. I now have SQL Server set to apply a default value of 0.25. However in my code I have

varVPFP5 = Nz(Forms!FrmPropYrFile!VoPTABfeeP5)

Shouldn't that have provided a zero if it was null on the form?

I don't want to wear out my questions but it really helps.

Phred

[CJ_London]

if the destination field isn't required (it can be null) why would it error out?

you can't insert nulls like this - see this link

https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

in particular

You must specify each of the fields of the record that a value is to be assigned to and a value for that field

null is not a value so you either need to assign a zero length string or 0 depending on datatype or have code to remove the field from the destination fields

[CJ_London]

varVPFP5 = Nz(Forms!FrmPropYrFile!VoPTABfeeP5)

Shouldn't that have provided a zero if it was null on the form?

nz returns a zero length string unless otherwise specified - see this link

https://support.office.com/en-nz/art...a-7fd9f4c69b6c

Agreed, it should return 0 in context so in theory if varVPFP5 has been declared as a number (from your naming it implies variant) it should be assigned 0, but in my experience it cannot always be relied on so I always state what to return i.e.

varVPFP5 = Nz(Forms!FrmPropYrFile!VoPTABfeeP5,0)