I am trying to have a DLookup with two criteria. I am able to make it work when I try only one criteria at a time.
First Criteria (works well)
=DLookUp("[InvoicesQry]![Qty]","[InvoicesQry]"," [InvoicesQry]![ContractNumber] ='" & [Forms]![OrderForm]![ContractNumber] & "'")
Second Criteria (works well)
=DLookUp("[InvoicesQry]![Qty]","[InvoicesQry]"," [InvoicesQry]![ItemNumber] = '" & [Forms]![OrderForm]![ItemNumber] & "'")
I have tried several ways to put them together with no success. Below is the closest I have got in my trials. I do not get any errors but it does not retrieve any values and, when I add NZ with 0 value, it gives me 0.
=DLookUp("[InvoicesQry]![Qty]","[InvoicesQry]"," [InvoicesQry]![ContractNumber] ='" & [Forms]![OrderForm]![ContractNumber] & " And [InvoicesQry]![ItemNumber]) =" & [Forms]![OrderForm]![ItemNumber] & "'")
Any help will be appreciated.