runSQL Command - Date Criteria Prevents Update

[Kiwiman]

I am trying to update a field in a table called tblActuallabour, based on the EmloyeeId and the WorkDate.

If I omit WorkDate criteria, the SQL will run but will update every record for the selected employee. This I do not want to happen.

If I include the criteria about the workdate, nothing happens, so the problem must be around this part of the code.

The table tblActualLabour does have a workdate the same as the forms combobox cboWeekEnd

I have tried different variations, but I am not getting anywhere. Any help would be appreciated.

Private Sub ContPaid() 'Mark Contractor Payments as Paid

On Error GoTo Err_ContPaid

Dim strSQL As String

strSQL = "UPDATE tblActualLabour SET tblActualLabour.Paid = -1 " & _
         "where tblActualLabour.Employeeid = " & [Forms]![frmContractorPayments]![cboContractor] & "" & _
            " AND tblActualLabour.WorkDate = #" & [Forms]![frmContractorPayments]![cboWeekEnd] & "#"


Call SETWOFF
DoCmd.RunSQL (strSQL)
Call SETWON

Exit_ContPaid:
    Exit Sub
    
Err_ContPaid:
    MsgBox Err.Description
    Resume Exit_ContPaid

End Sub

 

[RoyVidar]

What kind of date format are you using? If it is not US, then you need to format it in an unambiguous way, see International Dates in Access form more info. I'm using:

[tt] ...WorkDate = #" & format$(Forms!frmContractorPayments!cboWeekEnd, "yyyy-mm-dd") & "#"[/tt]

Also, ensure the date control actually contains a valid date.

Another thing that might provide this, is that either the date control, or the field contains a time fraction ( - say it's populated through Now() in stead of Date()...)

Roy-Vidar

 

[Kiwiman]

Thanks very much Roy-Vidar - the formatting worked a treat.