Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links
Compound Dlookup Function

Dlookup Function Example
Compound Dlookup Code
DMin & DMax Examples
DStDev & DStDevP Function


Microsoft Office VBA :
  MS Access 2003
  Access 2007
  Access 2010
  Access 2013
  Access 2016

Compound Dlookup Example

Microsoft Access Nested DLookup Domain Aggregate Function

CLICK FOR Free Discount Coupons

This example shows how you can use nested Dlookup functions to get nested table results in a relational hierarchy. What this example shows is multiple Dlookup functions within one.

Here are the tables in this example:

Nested Dlookup Function Vlookup We will have a query based only on the M_Customer_Orders table:

SELECT M_Customer_Orders.Customer_ID, M_Customer_Orders.Product_ID,
M_Customer_Orders.Order_Date, M_Customer_Orders.Qty
FROM M_Customer_Orders;

You did not know that nearly 100 cable and satellite tv installers use our software to manage their device inventory. They never lose track of an expensive device.

Did you know that most template databases often do not do exactly what you need done? We can create a custom template that fits your needs exactly.

The form design is as follows:

The control source compound dlookup code is much longer than can be displayed in design mode of the form.  I use notepad to construct the code and the copy and paste it into the control source.  Here is how the code looks when in notepad:

=Dlookup("Contact_Method","L_Contact_Methods","Contact_Method_ID=" &
Dlookup("Contact_Method_ID","L_Orgs","Org_ID=" &
Dlookup("Org_ID","M_Customers","Customer_ID=" & nz(Customer_ID,0))))

You can see from the code that we work our way from left to right starting with the end result and working are way down to the ID field available to us on the form (Customer_ID).  With the customer_ID we can obtain the Org_ID from the M_Customers table.  Then, we get the Contact_Method_ID for that customer from the L_Orgs table.  Easy isn't it!  Below is the final result:

Contact Information

Copyright 2000-2018 Blue Claw Database Design