[terrynbama]

I'm working on a programm which is inserting data into an Access2000 database. The following code creates a "syntax error in the Sql"

Dim lngContractSchedule As Long
Dim intWearerNumber As Integer
Dim strSize As String
Dim strBeginDate As Date
Dim strStatusDate As Date
Dim strItemCode As String
Dim intQuantity As Integer
Dim strGrade As String
Dim intChanges As Integer
Dim intSets As Integer
Dim intCTDused As Integer
Dim intCTDhigh As Integer
Dim strSalesCode As String
Dim curPrice As Currency
Dim curReplacementCharge As Currency

strSize = "1"
strBeginDate = Date
intQuantity = 1
strGrade = "U"
intChanges = 1
intSets = 1
intCTDused = 1
intCTDhigh = 1
strSalesCode = "N"
curReplacementCharge = 0
curPrice = 6.50
intSets = 1
intChanges = 1
strItemCode = "8000"


SQLData = "INSERT INTO ContractWearerItem(ContractNumber,WearerNumber,ItemCode,Size,Qty,Grade," _
& "NumberofSets,NumberofChanges,Price,ReplacementCharge,CTDused,CTDHighQty,BeginServiceDate,SalesCode)" & "VALUES(  " & lngContract & " , " & intWearerNumber & " , '" & strItemCode & "' ," _
& " '" & strSize & "' , " & intQuantity & " ," _
& " '" & strGrade & "' , " & intSets & " , " & intChanges & " , " & curPrice & " ," _
& " " & curReplacementCharge & " ," _
& " " & intCTDused & " , " & intCTDhigh & " , '" & strBeginDate & "' , '" & strSalesCode & "');"
rsData.Open SQLData, cnData, 3, 3

Through trial and "error" I've determined the problem seems to be the table fielname "Size". It is a primary key field in the table. If I change the name in the table to "Sizea", the sql runs fine.

I really can't change the name because the database is part of a business system programm written by someone else. They set up the tables and have many programs using the tables. So that is out of the question.

Is "Size" a reserved name? If so, I don't see how the other programmer is using it.
Is there a way to fix this problem?

[JackOfAllTrades]

If you're having trouble with a dynamically-generated query, I suggest printing it out somewhere on error so you can verify that it's as you expect. As to Size being a reserved word, it does not appear so (found by a Google search of "access 2000 reserved words", first link returned).

[Ionut]

Put the reserved word between square brackets and your query should run fine.

 INSERT INTO Table ([Size] .... 

[terrynbama]

It must have been a reserved word. The brackets worked great. Thanks