Trouble with comparing date fields
I have a program (code is below) which writes records to a table only if a record with matching keys doesn't already exist. The problem is, when I compare the date field, it never matches, so a new record gets written every time, even if a matching record exists. I have a SQL Server backend and am working with Access 2003 on Small Business Server 2003. Does anyone have a suggestion?
Private Function Write_Prod_Run() As Boolean
On Error GoTo Err_Write_Prod_Run
Write_Prod_Run = False
strRcdSrc = "Select * From ProductionRuns " & _
"Where ProdID = '" & txtProdID & "' and MachID = '" & txtMachID & _
"' and EmpID = '" & txtEmpID & "' and ProdDate = " & _
Format(txtProdDate, mmddyyyy) & " and Shift = " & frmShift & ";"
rstProdRun.Open strRcdSrc, cnn, adOpenDynamic, adLockOptimistic
With rstProdRun
If .EOF = True Then 'Record not found, insert new record.
.AddNew
.Fields("ProdID") = txtProdID
.Fields("MachID") = txtMachID
.Fields("EmpID") = txtEmpID
.Fields("ProdDate") = Format(txtProdDate, mmddyyyy)
.Fields("Shift") = frmShift
.Update
End If
End With
Write_Prod_Run = True
Exit_Write_Prod_Run:
rstProdRun.Close
Exit Function
Err_Write_Prod_Run:
MsgBox Err.Description
Resume Exit_Write_Prod_Run
End Function
|