DLookup with 2 filter criteria

[jenni_p]

Hello -
Is it possible to write a DLookup with 2 filter criteria?

For example, I have a query set up which places my
products in "categories" based on 2 variables
1) Destination_id
2) Product_Id

So when a user creates a purchase order, I want the
category to automatically pop up based on the destination
and product selected.

So here is where I am stuck;

Dim strfilter As String

strfilter = ????????????????????

Me!CategoryName = DLookup("Category", "qry_categories",
strfilter)

Any ideas?

Thanks in advance for the help!
Jenni

 

[Guest]

Try:

strfilter = "Destination_id = " & Destination_id & " and
Product_ID = " & Product_id

 

[John Vinson]

Hello -
Is it possible to write a DLookup with 2 filter criteria?

Only one filter criterion - but it can be essentially as complicated
as you wish. The third argument to any of the domain functions should
be a text string which is a valid SQL query WHERE clause, without the
word WHERE - and it can have ANDs, ORs, LIKE, or any query operator
you'ld use in a query.

For example, I have a query set up which places my
products in "categories" based on 2 variables
1) Destination_id
2) Product_Id

So when a user creates a purchase order, I want the
category to automatically pop up based on the destination
and product selected.

strfilter = "[Destination_ID] = " & Me!txtDestination _
& " AND [ProductID] = " & Me!txtProduct

Given arbitrary values in the form controls, this would end up

[Destination_ID] = 31 AND [ProductID] = 882

Note that blanks are important - for instance, there must be blanks on
both sides of the word AND, otherwise your string ends up like

[DestinationID] = 31AND ...