DLookup with And condition!!!

[whiteboy]

I need to know,Is it possible to use DLookup with And condition?

Because I tried folowing vba code in my Access database which gave me a Data Type Mismatch Error.The code used myself is as follows;

Dim EMP As Long
'EMP = DLookup("[ID]", "tbl_Test", "[ID] = '" & Me.txtEmpNo & "' And EMPNAME = " & Me.txtName)
End Sub

My requirement is to check duplicate records in two fields of single table.

 

[plog]

Yes you can, however, you should be using a DCount. Its guaranteed to return a number where DLookup can be null.

When you build a criteria string, you need to make sure single quotes surround the value of your string variables. Me.txtName has no such quote marks around it whereas Me.txtEmpNo does, but probably doesn't need them if its a number.

 

[whiteboy]

Yes you can, however, you should be using a DCount. Its guaranteed to return a number where DLookup can be null.

When you build a criteria string, you need to make sure single quotes surround the value of your string variables. Me.txtName has no such quote marks around it whereas Me.txtEmpNo does, but probably doesn't need them if its a number.

Thank you very much!

Does DCount facilitate to check duplicate value with in two fields of a single table? Because EMP NO and EMP Name are interrelated entries.There for I want to check these two fields together.

 

[plog]

Does DCount facilitate to check duplicate value with in two fields of a single table? Because EMP NO and EMP Name are interrelated entries

Can you restate that in less fancy english? Perhaps with an example?.

 

[whiteboy]

Thank you!!
It works for me

Please

Thank you very much!

Does DCount facilitate to check duplicate value with in two fields of a single table? Because EMP NO and EMP Name are interrelated entries.There for I want to check these two fields together.

It works for me!!! Thank you for your fast response!!

Please see the codes

Private Sub cboEmp_Click()
Me.cboEmp = DLookup("[ID]", "tbl_EMPMAIN", "[ID]=" & Me.cboEmp)
Me.txtName = DLookup("[EMPNAME]", "tbl_EMPMAIN", "[ID]=" & Me.cboEmp)
End Sub

Private Sub Command4_Click()
Dim Street As String
Dim ID As String
Dim EMPNAME As String
Dim INDATE As Variant
If DCount("[EMPNAME]", "tbl_Test", "[ID]='" & Me!cboEmp & "' AND [INDATE]='" & Me!txtDate & "'") > 0 Then
msg = "You already have that OrderID and Customer_ID combination" & vbNewLine
msg = msg & "The record will now be undone"
MsgBox msg, vbExclamation, "System Duplication Message"
Me.Undo
Cancel = True
End If
DoCmd.GoToRecord , , acNewRec
End Sub

 

[Gasman]

You might want to refer to this thread?

Concatenating control values for SQL statement?

Hi everyone, Recently we have had a raft of users struggling to use the correct surrounding characters when trying to build a SQL statement or even a DCount criteria. It also appears quite regularly anyway.? I seem to recall someone wrote a function that did it all for you?, so you just use...

www.access-programmers.co.uk

Plus it is helpful to put the all the criteria into a string and then Debug.Print that string so see if the syntax is correct.?

HTH