Expression DLookup

[jstevens]

I'm having a challenge understanding the DLookup function and applying it to a Query Expression.

Does anyone have an DB example that you can share?

On line examples generally associate it with a form. I'm interested in using DLookup within a query.

Regards,
John

[Rudi]

Try these two web samples...

-- http://www.techonthenet.com/access/func ... lookup.php
-- http://office.microsoft.com/en-za/acces ... 28825.aspx

[Pat]

If you use any of the aggregate functions in a query it will slow the query substantially.

[jstevens]

Rudi,

Thanks for the links but I am still having a challenges.

I have attached a sample Access DB. The expected query results should be

qryResult.png

Thanks for taking a look,
John

Database1.zip

[Pat]

What you need to do is to join the tables in a query based on Item#.
See attached database. I have added a new query called Query1.
Is that what you want?

[jstevens]

Pat,

I'm interested in a DLookup solution but thanks for providing a suggestion. From what I have read DLookup should work.

I must be missing something with the DLookup solution.

Regards,
John

[Pat]

For a start your query will provide a Cartesian product of all records in A with all records in B? So you should join the tables at least to stop that.
The query you want will possibly look like this:

SELECT DISTINCT A.Account, A.[ITEM#], DLookUp("COLOR","tbl_Item","[ITEM#]=" & A.[ITEM#]) AS Color
FROM tbl_Main AS A INNER JOIN tbl_Item AS B ON A.[ITEM#] = B.[ITEM#]

Why do you wish to use DLookup??

[jstevens]

Pat,

The actual table I will be using has a number Items in tbl_Main that are not in tbl_Item plus a good number of other fields. The query results include a variety of fields one of which is the ITEM#. If I use a join between the two tables I will not be able to query all "Item#s"; just those which are joined i.e. Inner, Right or Left joins.

Since Access has the DLookup function I believe it works in the same manner as Excel's Index and Match functionality. I have reviewed the DLookup examples but can not get it to work therefore I posted the example DB in my Post=131076 inquiring about DLookup.

I am aware of the Cartesian product of all records but will reduce the query results by applying a WHERE statement between two dates; from/to dates.

Regards,
John

[HansV]

The solution provided by Rudi is MUCH more efficient than using DLookup. If you still want to use DLookup, however, you should NOT include tbl_Item in the query - you us DLookup to look up a value in this table, so including it in the query too is redundant.
As Pat has indicated, the third argument to DLookup is a string (just like the other two arguments). The SQL for the query looks like this:

SELECT DISTINCT Account, [ITEM#], DLookUp("COLOR","tbl_Item","[ITEM#]=" & [ITEM#]) AS Color
FROM tbl_Main;

A screenshot of design view:

S0876.png

And of the result:

S0877.png

[jstevens]

Hans,

Thank you for the detailed explanation. I now understand how the DLookup function works. One additional thing I did learn through this exercise is that the field data type comes into play. The expression you provided is based on the field data type being numeric.

The expressions changes slightly when the field data type is text. Color: DLookUp("COLOR","tbl_Item","[ITEM#]='" & [item#] & '"")

Rudi/Pat - I appreciate your contributions as well.

Regards,
John

[HansV]

The expressions changes slightly when the field data type is text.

That is correct. It's also different for date fields:

DLookup("SomeField", "MyTable", "MyDate=#" & Format([MyDate], "yyyy-mm-dd") & "#")

The date value that we compare to must be enclosed in # characters, and we must either use US date format mm/dd/yyyy, or the international unambiguous format yyyy-mm-dd.