DLookup with 2 filter criteria

  • Thread starter Thread starter jenni_p
  • Start date Start date
J

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
 
Hello -
Is it possible to write a DLookup with 2 filter criteria?
Click to expand...

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.
Click to expand...

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 ...
 
Back
Top