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

Asking the web for "ms access dlookup Data type mismatch error", would have shown the answer in seconds. Next question will be "ms access dlookup criterias" as actual criterias won't find any matches (even they exist). — ComputerVersteher, Sep 10 '20 at 21:08
Don't show images of text! Copy and paste the text (put code in code blocks as I did on my edit) ,so we can copy and paste too!! You asked the web for "ms access dlookup criterias"? — ComputerVersteher, Sep 11 '20 at 04:11

score 0 · Answer 1 · answered Sep 11 '20 at 14:47

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

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

1 Answers1