Here is my vba
Code:
UpdateSQL = "UPDATE dbo.JT_DailyTimeEntry SET EndTime = ""0000"", Overtime = ""N"", NewWTStatus = ""COM"", NewStatusComment = ""T/T COMPLETED"", NewStatusDate = '" & CurrentDate & "'," & _
" ActivityCode = """ & ActCode & """, WTPostWTStep = """ & StepNum & """, DepartmentWorkedIn = ""00"", WTInHistory = ""N"", EarningsCode = ""000001"", LaborBillingType = ""N""," & _
" SeqNoLDTrxRecord = """ & LDSequenceNo & """, HoursWorked = 0, PRHourstoPost = 0, QuantityCompleted = 0, LaborCost = 0, BurdenCost = 0, DilutedTime = 0, OverheadCost = 0," & _
" DateCreated = '" & CurrentDate & "', TimeCreated = """ & CurrentTime() & """, UserCreatedKey = """ & EmployeeNo & """, DateUpdated = '" & CurrentDate & "', TimeUpdated = """ & CurrentTime() & """, UserUpdatedKey = """ & EmployeeNo & """" & _
" WHERE ((TransactionDate = '" & CurrentDate & "') AND (DepartmentNo = ""00"") AND (EmployeeNo = """ & EmployeeNo & """) AND (RecordType = ""1"") AND (SalesOrderNo = """ & SONum & """) AND (WTNumber = """ & DHRNum & """) AND (WTStep = """ & StepNum & """) AND (SequenceNo = """ & sSequenceNo & """));"
Here is the debug.print of it as SQL
Code:
UPDATE dbo.JT_DailyTimeEntry SET EndTime = "0000", Overtime = "N", NewWTStatus = "COM", NewStatusComment = "T/T COMPLETED", NewStatusDate = '', ActivityCode = "", WTPostWTStep = "", DepartmentWorkedIn = "00", WTInHistory = "N", EarningsCode = "000001", LaborBillingType = "N", SeqNoLDTrxRecord = "", HoursWorked = 0, PRHourstoPost = 0, QuantityCompleted = 0, LaborCost = 0, BurdenCost = 0, DilutedTime = 0, OverheadCost = 0, DateCreated = '', TimeCreated = "9.05976", UserCreatedKey = "", DateUpdated = '', TimeUpdated = "9.05976", UserUpdatedKey = "" WHERE ((TransactionDate = '') AND (DepartmentNo = "00") AND (EmployeeNo = "") AND (RecordType = "1") AND (SalesOrderNo = "") AND (WTNumber = "") AND (WTStep = "") AND (SequenceNo = ""));
I keep getting the following error for each of the WHERE clauses except for the TransactionDate.
Code:
(207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '00'.
(207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'EmployeeNo'.
(207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '1'.
(207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'SalesOrderNo'.
(207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'WTNumber'.
(207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'WTStep'.
(207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'SequenceNo'.
I know the columns exist because I can open the table and see them. I have excluded some columns because I was getting an error saying it can't update values to a ZLS and that error went away when I just removed those columns from the statement.