I want to check duplicate values of two field in single table using Dlookup function

Question

enter image description hereI need to check duplicate values in two fields on a single table. I need to check duplicate entries for particular employers' EMPNo (Employer Number) and EMPDATE(Employer Attendance Date).

I used the following code but it gives "Data type mismatch error"

My exsisting DLookup function is as follows;

Private Sub Command41_Click()
Dim EMP As String
EMP = DLookup("[EMPNO]", "[tbl_LEAVE]", "[EMPNO] = '" & Me.txtEMPNO & "' And LEAVEDATE= " & Me.txtDate)

End Sub

Answer 1

Your issue is, that the date criteria must be formatted properly. Also, DLookup returns Null when not found, and Null cannot be assigned to a string. Thus, apply Nz:

Private Sub Command41_Click()

    Dim EMP As String

    EMP = Nz(DLookup("[EMPNO]", "[tbl_LEAVE]", "[EMPNO] = '" & Me.txtEMPNO & "' And LEAVEDATE = #" & Format(Me.txtDate, "yyyy\/mm\/dd") & "#"))

End Sub