Dlookup with multiple condition...

[anbusds]

Hi All,
I Have one main form in access 2003 i want to use Dlookup function to 1 text box with 2 criteria is it possible?
My Formula:
Text67. Value = DLookup("Balance", "dbo_WbookEdit", "[Lotno] = '" & Forms![Edit]![LotNo] & "'" And " & [Description] = '" & Forms![Edit]![Description] & "'")

Thanks in advance!!!

 

[pr2-eugin]

Are you using this in VBA or on the Form's Design view?

 

[anbusds]

Yes in VBA Paul Eugin...

 

[pr2-eugin]

So something like this?

Private Sub Form_Current()
    Me.Text67 = Nz(DLookup("Balance", "dbo_WbookEdit", "[Lotno] = '" & Me.LotNo & _
                          "' And [Description] = '" Me.Description & "'"), "N/A")
End Sub

 

[anbusds]

Hi,
I Found the solution...
Code as follows,

Dim strSQL As String
strSQL = "[LotNo] = '" & Me![LotNo] & "'" & " And [Description] = '" & Me![Description] & "'" & " And [Invoice] = '" & Me![Invoice] & "'" & " And [Customer] = '" & Me![Customer] & "'"
dlook.Value = DLookup("LotNo", "dbo_WBookEdit", strSQL)
If dlook.Value > 0 Then
MsgBox "Duplicate Entry"
End If

Regards
Anbu

 

[pr2-eugin]

Would suggest one alteration, use DCount instead of DLookup.

Dim strCriteria As String

strCriteria = "[LotNo] = '" & Me![LotNo] & _
              "' And [Description] = '" & Me![Description] & _
              "' And [Invoice] = '" & Me![Invoice] & _
              "' And [Customer] = '" & Me![Customer] & "'"

If DCount("*", "dbo_WBookEdit", strCriteria) <> 0 Then
    MsgBox "Duplicate Entry"
End If

 

[anbusds]

Working Well