Dlookup with multiple criteria

[ppataki]

Dear All,

I would like to use dlookup with 2 criteria
I don't want to concatenate these two criteria, I would like to evaluate them together

Here is my function in VBA:
Me.profitcombo = DLookup("[profitratiorank]", "[tbl_annualdata]", "[YearData] = " & Me.yearcombo And "[Custname] = '" & Me.customer & "'")
The above function is supposed to extract a field that matches both criteria at the same time
Instead I get the following error message: Type mismatch
I checked the data types of all the fields and they are the same

Could you please advise?
Many thanks in advance!

 

[John Big Booty]

From here;

Specifying Multiple Fields in the Criteria:
The criteria expression can be any valid SQL WHERE clause (without the keyword WHERE). This implies that more than one field can be used to specify criteria for a DLookup() function.

To find the OrderID for one of the orders sold by employee "Andrew Fuller," with an EmployeeID of 2 (numeric), for customer "Simons bistro", with a CustomerID of SIMOB (textual), use the following sample DLookup() statement:

=DLookUp("[OrderID]", "Orders", _
      "[CustomerID] = 'SIMOB' And [EmployeeID] = 2")

 

[Avelino]

beyond beautiful example of John, you left out "and"

Me.profitcombo = DLookup("profitratiorank", "[tbl_annualdata]", "YearData = " & Me!yearcombo & " And Custname = '" & Me!customer & "'")

Sucess