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

First thing that struck me was that if [ProdDate] is defined as a Date/Time field you need date littorals (#) around the date e.g.

ProdDate = #" & _
  Format(txtProdDate, mmddyyyy) & "# ..... etc

SQL also insists on dates in US format, but you seem to have that covered

Cheers

Alan t

Hi Jimmy and Alan,

Alan, you missed something I've missed a bunch before too, but Jimmy is using SQL Server as a backend, not Access.

So Jimmy, the date parameter you're passing to SQL Server is a string literal, and will therefore need to be delimited by apostrophes also. The following will match your date field for you:

strRcdSrc = "Select * From ProductionRuns " & _
  "Where ProdID = '" & txtProdID & "' and MachID = '" & txtMachID & _
  "' and EmpID = '" & txtEmpID & "' and ProdDate = '" & _
  Format(txtProdDate, mmddyyyy) & "'"

This will produce a SQL statement like:

Select *
From ProductionRuns
Where ProdID = '1'
and MachID = '1'
and EmpID = '1'
and ProdDate = '1/1/2001'

Notice that the date is delimited by apostrophes also.

HTH,

Bob

Thanks Bob! The quotes did the trick. I had tried enclosing the date with #s, but SQL Server doesn't like it.

:D