Access & VB's Missing Domain Lookup Functions

Introduction

Many Access users try, at one time or another, to use domain lookup functions to answer questions which are just outside of their application range. Many ideas present themselves, but after testing them and reading the relevant help pages, nothing seems to work. But it always seems so close!

[ Read on Experts-Exchange ]

Question, by Asker: I need to find the last appointment of a patient.

Expert: Sure: DMax("AppDate", "Appointments", "PatientID=101") -- Enjoy!

Asker: Thanks, but that gives me the date, I need the appointment ID.

Expert: Oh... wait... I think I got it: You need to get the date like before, store it in a date variable (unless there is no previous date, then it would have to be a variant, and you can test for Null). Say the variable is datPrev, then lookup the ID, with something like this : DLookup("AppointmentID", "Appointments", "PatientID=101 And AppointmentDate = #" & datLast & "#"). Notice the #, this is needed for dates. -- Good Luck!

Expert: Typo. Meant datPrev.

Asker: Huh? This seems awfully complicated. What do I type in the text box?

Member: Just use DLast("AppointmentID", "Appointments", "PatientID=101")

Asker: Thanks, Member! Exactly what I was looking for!

Expert: With all due respect, Member, have you read the help page on DLast? E.g. the words "return[s] a random record " and "[use it] when you simply need any value from that field"?

Member: Try it!

You can imagine the rest. DLast() fails after waisting everybodys time, Expert2 comes in to suggest a user defined function, Expert3 thinks that this should be done in a query, Expert1 tries to nest DMax() into DLookup() in a single expression while correcting Expert2's function...

Asker: It should be simple: I need the last appointment of a patient. Why isn't there a function for that?

This is the typical "I need the last (first) of something" question, with one thousand answers. It's frustrating because a clever mixture of aggregate and lookup functions can do the job, but the expression looks ugly and inefficient.

[ Another day on Experts-Exchange ]

Question, by Asker: I have a table with players, and I want the names of the players from Texas, with 'TX' in the field State.

Expert: SELECT FullName FROM TableWithPlayers WHERE State='TX'

Asker: Thanks. Is this a query?

Expert: Yes, just paste it into a new query in SQL view, after adjusting table and field names.

Asker: I need this in a text box.

Expert: Perhaps you can use a list box. Paste the SQL as "record source".

Asker: I just want a list, like: Smith, Adams, Kruger.

Expert: Oh... You will need to build the list using a function. Is this for a report? You could use the Format event and...

Again, is there no function to do this in Access?


This article contains two generic functions: DFind() and DList(), both of which could easily have been added to the family of "domain functions" from the start.

For VB developers using a Jet database, a second version also includes an implementation of the entire family, from DLookup() to DVarP(), not forgetting the ignominious DFirst() and DLast().



Technical Background

While gathering data for a related article, Access Techniques: Fast Table Lookup Functions, I discovered that a user defined implementation of DLookup() is just as fast as the built-in version -- even a little faster. Also, it became obvious that all aggregate functions are merely convenient ways to call DLookup():

    DMax("OrderDate","Orders") == DLookup("Max(OrderDate)","Orders")

Since DLookup() returns a single record, this also means that DLookup is identical with DFirst(). The worst of the family is of course DLast(), because "last" doesn't mean what it implies and because it sometimes seems to work!

I realised that I didn't have to endure built-in functions if I felt they needed improvement. I could fix the major problem of DLookup & Co. without any loss in performance. The problem, identical to that of the SQL First() and Last() aggregate functions, is that they offer no way to control the sort order of the records.

When several records match the criteria, a random record is returned.

I'm not saying "random" in the mathematical sense. I'm just saying it's unpredictable. There are some rules, and the same call twice in a row returns the same value, but not necessarily after data insertion and deletion, or after compacting the database.

What should happen when several records match is one of three things:
Returning "any random record" is most of the times perfectly pointless.

Simply import the module below, and the next time you are annoyed with DLookup(), switch to DFind() or DList(), without any loss in performance. The arguments are almost entirely compatible, but not quite (please read the documentation below).

Final note: if your application relies heavily on lookup functions, you might want to read the article mentioned at the top of this section. Many functions can be rewritten using "fast table lookup" techniques, with a spectacular improvement in speed.



The Functions DFind() and DList()
and all other missing domain functions for VB applications

To include the functions below in your project, create a new module (not a class module), delete the options at the top (they would be duplicated otherwise), and paste the content of the code frame below into it.

Save the module. 'Module1' is a fine name; if you want to change it, do not call the module 'DFind' or 'DList', as that would conflict with the functions. You can use 'basLookup', or 'Module from EE' safely.

Compile: from the Visual Basic menu, choose (Debug | Compile <project name>); nothing should happen, meaning there were no typos or syntax errors. Save again.

Now, wherever you used a domain function, you can also use DFind() and DList(): in VB code (naturally), as control source of a text box on forms and reports, even in a query.

'-------------------------------------------------------------------------------
                      ' "Missing lookup functions" -- Access version
                      ' Written for Experts Exchange: www.experts-exchange.com
                      ' Article and help page: www.experts-exchange.com/A_2011.html
                      ' Copyright Markus G Fischer, Geneva 2009
                      '-------------------------------------------------------------------------------
                      '
                      ' This module implements two original lookup functions, DFind() and DList()
                      '
                      ' Both are similar to DLookup, but provide a solution when several records
                      ' match the criteria: selecting the first using a specific sort order, or
                      ' showing them all in a list.
                      '
                      ' Optional reference: Microsoft DAO ?.? Object Library
                      ' allows replacing "Options:=4" with "Options:=dbReadOnly"
                      '
                      Option Explicit
                       
                      ' Settings for the functions
                      Const SHOW_ERROR    As Boolean = True       ' Return Error or Null on Error?
                      Const MAX_LIST      As Integer = 100        ' Limits the items for DList()
                       
                      ' DFind(Expr, Domain, [Criteria], [Order])
                      '
                      ' Similar to DLookup, with an additional argument. If several records match
                      ' the Criteria, the Order argument is used to pick one.
                      '
                      ' Example: Name of the youngest employee
                      ' ? DFind("FirstName", "Employees", Order:="BirthDate DESC")
                      '
                      Function DFind(Expr As String, Domain As String, _
                          Optional Criteria = Null, Optional Order = Null)
                          
                          Dim strSQL As String
                          Dim intN as Integer
                          
                      On Error GoTo Failure
                          
                          strSQL _
                              = " SELECT TOP 1 " & Expr _
                              & " FROM " & Domain _
                              & " WHERE " + Criteria _
                              & " ORDER BY " + Order
                              
                          DFind = Null
                          With DBEngine(0)(0).CreateQueryDef("", strSQL)
                              For intN = 0 To .Parameters.Count - 1
                                  .Parameters(intN) = Eval(.Parameters(intN).Name)
                              Next intN
                              With .OpenRecordset(Options:=4)
                                  If .RecordCount Then DFind = .Fields(0)
                              End With
                          End With
                          Exit Function
                          
                      Failure:
                          If SHOW_ERROR Then DFind = CVErr(Err.Number)
                          Err.Clear
                          
                      End Function
                       
                      ' DList(Expr, Domain, [Criteria], [Order], [Sep])
                      '
                      ' Similar to DFind, but returning Expr for each record found as a list, using
                      ' the argument Sep as separator.
                      '
                      ' Example: alphabetised list of Products for a Supplier
                      ' ? DList("ProductName", "Products", "SupplierID=1", "ProductName", vbCrLf)
                      '
                      Function DList(Expr As String, Domain As String, _
                          Optional Criteria = Null, Optional Order = Null, _
                          Optional Sep As String = ", ")
                          
                          Dim strSQL As String
                          Dim intN As Integer
                          
                      On Error GoTo Failure
                          
                          strSQL _
                              = " SELECT " & Expr _
                              & " FROM " & Domain _
                              & " WHERE " + Criteria _
                              & " ORDER BY " + Order
                              
                          DList = Null
                          With DBEngine(0)(0).CreateQueryDef("", strSQL)
                              For intN = 0 To .Parameters.Count - 1
                                  .Parameters(intN) = Eval(.Parameters(intN).Name)
                              Next intN
                              With .OpenRecordset(Options:=4)
                                  intN = 0
                                  Do Until .EOF
                                      If intN > MAX_LIST Then
                                          DList = DList + Sep & "..."
                                          Exit Do
                                      End If
                                      DList = DList + Sep & .Fields(0)
                                      intN = intN + 1
                                      .MoveNext
                                  Loop
                              End With
                          End With
                          Exit Function
                          
                      Failure:
                          If SHOW_ERROR Then DList = CVErr(Err.Number)
                          Err.Clear
                          
                      End Function
                      

Select all Open in new window

(open in new tab)

The module for stand-alone Visual Basic implements both new functions, with a few additions. No need to import both modules.

It contains an implementation of CurrentDb, a property returning a static open database object, which is used in the functions. If you already have such an object, you can use yours instead.

It also contains an implementation of Nz() and the entire family of domain functions, implemented as calls to DFind(). They are as compatible with the built-in Access functions as possible.

'-------------------------------------------------------------------------------
                      ' "Missing lookup functions" -- VB stand-alone version
                      ' Written for Experts Exchange: www.experts-exchange.com
                      ' Article and help page: www.experts-exchange.com/A_2011.html
                      ' Copyright Markus G Fischer, Geneva 2009
                      '-------------------------------------------------------------------------------
                      '
                      ' This module implements two original lookup functions, as well as the built-in
                      ' Access domain functions.
                      '
                      ' These functions are implemented by the Access Application object, and are as
                      ' such not available in stand-alone VB or another flavour of VBA.
                      '
                      ' These functions operate on the CurrentDb, so a property with this name is
                      ' provided for readability. CurrentDb will return a DAO database object for
                      ' the application's default Jet database (see the constant below).
                      '
                      ' References: Microsoft DAO ?.? Object Library
                      '
                      Option Explicit
                       
                      ' Application's Jet Database:
                      Const CurrentDb_PATH As String = "C:\...\NorthWind.mdb"
                       
                      ' Settings for the functions
                      Const SHOW_ERROR    As Boolean = True       ' Return Error or Null on Error?
                      Const MAX_LIST      As Integer = 100        ' Limits the items for DList()
                       
                      Private dbJet       As DAO.Database         ' static database object
                       
                      ' CurrentDb -- Returns the current database
                      '
                      ' Replace with your own database object if needed
                      '
                      Public Property Get CurrentDb() As DAO.Database
                          If dbJet Is Nothing Then Set dbJet = DAO.OpenDatabase(CurrentDb_PATH)
                          Set CurrentDb = dbJet
                      End Property
                       
                      ' Nz(Value, [ValueIfNull]) -- compatible with Access' Nz() function
                      '
                      ' The way Empty can be returned is compatible with the Access implementation
                      '
                      Function Nz(Value, Optional ValueIfNull = Empty)
                          If Not IsNull(Value) Then Nz = Value Else Nz = ValueIfNull
                      End Function
                       
                      ' DFind(Expr, Domain, [Criteria], [Order])
                      '
                      ' Similar to DLookup, with an additional argument. If several records match
                      ' the Criteria, the Order argument is used to pick one.
                      '
                      ' Example: Name of the youngest employee
                      ' ? DFind("FirstName", "Employees", Order:="BirthDate DESC")
                      '
                      Function DFind(Expr As String, Domain As String, _
                          Optional Criteria = Null, Optional Order = Null)
                          
                          Dim strSQL As String
                          
                      On Error GoTo Failure
                          
                          strSQL _
                              = " SELECT TOP 1 " & Expr _
                              & " FROM " & Domain _
                              & " WHERE " + Criteria _
                              & " ORDER BY " + Order
                              
                          DFind = Null
                          With CurrentDb.OpenRecordset(strSQL, Options:=4)
                              If .RecordCount Then DFind = .Fields(0)
                          End With
                          Exit Function
                          
                      Failure:
                          If SHOW_ERROR Then DFind = CVErr(Err.Number)
                          Err.Clear
                          
                      End Function
                       
                      ' DList(Expr, Domain, [Criteria], [Order], [Sep])
                      '
                      ' Similar to DFind, but returning Expr for each record found as a list, using
                      ' the argument Sep as separator.
                      '
                      ' Example: alphabetised list of Products for a Supplier
                      ' ? DList("ProductName", "Products", "SupplierID=1", "ProductName", vbCrLf)
                      '
                      Function DList(Expr As String, Domain As String, _
                          Optional Criteria = Null, Optional Order = Null, _
                          Optional Sep As String = ", ")
                          
                          Dim strSQL As String
                          Dim intN As Integer
                          
                      On Error GoTo Failure
                          
                          strSQL _
                              = " SELECT " & Expr _
                              & " FROM " & Domain _
                              & " WHERE " + Criteria _
                              & " ORDER BY " + Order
                              
                          DList = Null
                          With CurrentDb.OpenRecordset(strSQL, Options:=4)
                              Do Until .EOF
                                  If intN > MAX_LIST Then
                                      DList = DList + Sep & "..."
                                      Exit Do
                                  End If
                                  DList = DList + Sep & .Fields(0)
                                  intN = intN + 1
                                  .MoveNext
                              Loop
                          End With
                          Exit Function
                          
                      Failure:
                          If SHOW_ERROR Then DList = CVErr(Err.Number)
                          Err.Clear
                          
                      End Function
                       
                      ' Implementation of the domain lookup functions found in Access
                       
                      Function DLookup(Expr As String, Domain As String, Optional Criteria = Null)
                          DLookup = DFind(Expr, Domain, Criteria)
                      End Function
                      Function DCount(Expr As String, Domain As String, Optional Criteria = Null)
                          DCount = DFind("Count(" & Expr & ")", Domain, Criteria)
                      End Function
                      Function DSum(Expr As String, Domain As String, Optional Criteria = Null)
                          DSum = DFind("Sum(" & Expr & ")", Domain, Criteria)
                      End Function
                      Function DAvg(Expr As String, Domain As String, Optional Criteria = Null)
                          DAvg = DFind("Avg(" & Expr & ")", Domain, Criteria)
                      End Function
                      Function DMin(Expr As String, Domain As String, Optional Criteria = Null)
                          DMin = DFind("Min(" & Expr & ")", Domain, Criteria)
                      End Function
                      Function DMax(Expr As String, Domain As String, Optional Criteria = Null)
                          DMax = DFind("Max(" & Expr & ")", Domain, Criteria)
                      End Function
                      Function DStDev(Expr As String, Domain As String, Optional Criteria = Null)
                          DStDev = DFind("StDev(" & Expr & ")", Domain, Criteria)
                      End Function
                      Function DStDevP(Expr As String, Domain As String, Optional Criteria = Null)
                          DStDevP = DFind("StDevP(" & Expr & ")", Domain, Criteria)
                      End Function
                      Function DVar(Expr As String, Domain As String, Optional Criteria = Null)
                          DVar = DFind("Var(" & Expr & ")", Domain, Criteria)
                      End Function
                      Function DVarP(Expr As String, Domain As String, Optional Criteria = Null)
                          DVarP = DFind("VarP(" & Expr & ")", Domain, Criteria)
                      End Function
                       
                      ' Two versions are available for DFirst() and DLast():
                      ' (change True to False, or delete the unwanted version)
                      '
                      #If True Then   ' Access compatibility
                      ' Similar to built-in implementation (and just as useless):
                      Function DFirst(Expr As String, Domain As String, Optional Criteria = Null)
                          DFirst = DFind("First(" & Expr & ")", Domain, Criteria)
                      End Function
                      Function DLast(Expr As String, Domain As String, Optional Criteria = Null)
                          DLast = DFind("Last(" & Expr & ")", Domain, Criteria)
                      End Function
                      #Else   ' NOT compatible with Access
                      ' Somewhat smarter: auto-sorts by the Expr argument
                      Function DFirst(Expr As String, Domain As String, Optional Criteria = Null)
                          DFirst = DFind(Expr, Domain, Criteria, Expr & " ASC")
                      End Function
                      Function DLast(Expr As String, Domain As String, Optional Criteria = Null)
                          DLast = DFind(Expr, Domain, Criteria, Expr & " DESC")
                      End Function
                      #End If
                      

Select all Open in new window

(open in new tab)



Test Database

It is good practice to perform tests of a new tool in a scratch database. The file below is a database in Access 2000 format, containing all the table of the "Northwind Traders" demo database, from the 97 version, which explains the ranges of all dates. The tables are used in many examples, starting with the help pages themselves. All the examples below refer to these tables.
NorthWind97.mdb



Help page for DFind()

This function is so similar to DLookup() that it's tempting to copy the help page and reproduce it here. I will resist, and urge you to read that page instead.

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
DFind(Expr, Domain, [Criteria], [Order])

Returns a single value based on the arguments:

Expr is an expression. It is often simply a field name from domain, but can include calculations, string operations, and formatting. All built-in and user-defined functions are available, as well as Access objects such as controls on a form. If needed, enclose your field names with square brackets.

Domain describes the records being searched. It is normally the name of a table or of a saved query. If needed (if it contains spaces or other invalid characters), the name must be enclosed in square brackets. This is the major difference with DLookup, which will add them for you. Unlike DLookup's domain argument, you can also specify multiple tables as source, as demonstrated in the "Advanced Tricks" examples.

Criteria is an optional string containing a logical expression using fields from domain. Only the records that satisfy the condition can be returned. Building a valid criteria is sometimes complicated, see the "Criteria Examples" below. Fundamentally, a criteria is a query's WHERE clause without the word "where".

Order is the additional optional argument, specifying how the found records should be sorted. It is a list of fields or expressions, separated by commas, each followed optionally by the keywords 'ASC', for ascending -- the default, or 'DESC', for descending. DFind returns only one value, the one at the top of the ordered list. When order is left blank and several records match the criteria, any available record is used.

When no records are found, the function returns Null. If an error occurs, both syntax errors and run-time errors, the function returns a Variant with subtype Error, containing only the error number. In Access, this number can be translated using AccessError(). This method of error handling is best suited for functions called from queries or forms, since only the message '#Error' is displayed, without interrupting the code. If you prefer that the functions return Null on error, set the constant SHOW_ERROR to False at the top of the module.
____________________

DFind Examples

All examples are based on the tables from the NorthWind demo database, much like most of the Access help pages. Each example starts with a question mark, this makes it easy to copy examples to the Immediate Pane.

The first examples are meant to show the limitations of the built-in functions.

' Product name by its ID  --  a task fit for DLookup

  ? DLookup("ProductName", "Products", "ProductID=14")
  ? DFind("ProductName", "Products", "ProductID=14")   ' compatible
  ? DList("ProductName", "Products", "ProductID=14")   ' single item list

' Any product name by Supplier ID  --  unpredictable result

  ? DLookup("ProductName", "Products", "SupplierID=1")
  ? DFirst("ProductName", "Products", "SupplierID=1")
  ? DLast("ProductName", "Products", "SupplierID=1")

This shows how the additional order clause can provide meaningful results.

' "Real" first/last product names by their ID

  ? DFind("ProductName", "Products", "SupplierID=1", "ProductID")
  ? DFind("ProductName", "Products", "SupplierID=1", "ProductID Desc")

Aggregate functions can provide simple "first/last" answers.

' First/last product names in alphabetical order

  ? DMin("ProductName", "Products")
  ? DMax("ProductName", "Products")

' Equivalent to:

  ? DFind("ProductName", "Products", Order:="ProductName")
  ? DFind("ProductName", "Products", Order:="ProductName Desc")

Also with a criteria:

' First product name by supplier ID

  ? DMin("ProductName", "Products", "SupplierID=1")
  ? DFind("ProductName", "Products", "SupplierID=1", "ProductName")

DFind can handle more subtle questions:

' Cheapest / most expensive product name by supplier ID

  ? DFind("ProductName", "Products", "SupplierID=1", "UnitPrice")
  ? DFind("ProductName", "Products", "SupplierID=1", "UnitPrice DESC")

' Youngest employee's name

  ? DFind("FirstName", "Employees", , "BirthDate DESC")

' Senior London employee

  ? DFind("LastName", "Employees", "City='London'", "BirthDate")

' Previous order for the same customer (Order 10400, customer 'EASTC')

  ? DFind("OrderID", "Orders", "OrderID<10400 And CustomerID='EASTC'", "OrderID DESC")

' Last Order, last shipped order, next required order

  ? DFind("OrderID", "Orders", , "OrderDate DESC")
  ? DFind("OrderID", "Orders", , "ShippedDate DESC")
  ? DFind("OrderID", "Orders", "ShippedDate Is Null", "RequiredDate")



Help page for DList()

This function is used to produce a list from matching records. This is the second solution to the problem of multiple matches; the sort order remains meaningful, naturally.

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
DList(Expr, Domain, [Criteria], [Order], [Sep])

Returns a list instead of a single value, using arguments identical to DFind(), with the following differences:

Expr is an expression, as before. However, it can contain the keyword 'DISTINCT' before the expression; this will remove duplicates from the list. As a side effect, it also sorts the list in ascending order (alphabetical or numerical, depending on the expression).

Domain and criteria are unchanged.

Order does not work in conjunction with 'DISTINCT', except to reverse the sort order. For example, if the expr argument is "DISTINCT Country", then the order can only contain "Country DESC".

Sep is the list separator. It defaults to a comma and a space. Typical choices are "; ", "/", and vbCrLf or Chr(13)+Chr(10) to obtain a list with each item on a new line.

The number if items returned is limited to 100; this can be adjusted in the module by changing the constant MAX_LIST. The constant SHOW_ERROR has the same meaning and effect as for DFind().
____________________

DList Examples

As before, all examples are based on the NorthWind tables. They should be mostly self-explanatory.

' List of employees: unsorted and sorted

  ? DList("LastName", "Employees")
  ? DList("LastName", "Employees", , "LastName")

' With full names, one per line

  ? DList("FirstName+' '&LastName", "Employees", , "LastName,FirstName", vbCrLf)

' Cities of all employees (with and without duplicates)

  ? DList("City", "Employees", , "City")
  ? DList("DISTINCT City", "Employees")

' List of beverages (category 1), then sorted alphabetically, and by price

  ? DList("ProductName", "Products", "CategoryID=1")
  ? DList("ProductName", "Products", "CategoryID=1", "ProductName")
  ? DList("ProductName", "Products", "CategoryID=1", "UnitPrice DESC")

' Same with prices, one per line

  ? DList( _
      Expr:="ProductName & ' - ' & Format(UnitPrice,'Currency')", _
      Domain:="Products", _
      Criteria:="CategoryID=1", _
      Order:="UnitPrice DESC", _
      Sep:=vbCrLf)

' List of categories, in "row source" format of a combo or listbox

  ? DList("CategoryID & ';'''+CategoryName+''''", "Categories", Sep:=";")

' Orders awaiting shipping, proritised

  ? DFind("OrderID", "Orders", "ShippedDate Is Null", "RequiredDate")



Criteria Examples

This argument is a string. It's either a static string delimited by quotes, or an expression that will build a string before the function is called. In Visual Basic, all strings are quoted with double-quotes. In Jet and on controls in forms and reports, both single-quotes and double-quotes can be used.

    "ProductID = 11"  --  static string
    'ProductID = 11'  --  alternative quoting used in a control

    "ProductID = " & Me.cboProduct  --  Visual Basic example
    'ProductID = ' & [cboProduct]  --  text box "control source" example

A string similar to the first will be built, using the current value of the combo box. If that value is Null, the criteria "ProductID = " will generate a syntax error. In a text box, this would normally be displayed as '#Error'.

In a double-quoted string, you can use sing-quotes for sub-strings. (The reverse is also true.)

    "City = 'London'"
    "CompanyName Between 'A' And 'N'"
    "CompanyID = '" & txtCompany & "'"   >>>  CompanyID = 'ALFKI'

Dates are quoted using the '#' symbol, in either US or ISO format. This is a problem, because in the Application space, dates will be formatted according to regional settings. The following "lazy" syntax will work in the US and in Japan, but not in Europe.

    "ShippedDate >= #" & txtMinDate & "#"
        >>>  ShippedDate >= #8/1/1996#  --  1st of August
        >>>  ShippedDate >= #1996-08-01#  --  unambiguous
        >>>  ShippedDate >= #1-8-1996#  --  read as the 8th of January by Jet!

Formally, all dates passes as parameter should be formatted, using ISO dates for readability, or simply converted to a date serial number.

    "ShippedDate >= #" & Format(txtMinDate, "yyyy\-mm\-dd") & "#"
    "ShippedDate >= ' & CLng(txtMinDate)  >>>  ShippedDate >= 35278

When a date function is used internally, there are no formatting issues:

    "RequiredDate >= Date()"
    "ShippedDate > DateAdd('ww', 5, OrderDate)"

Much more could be said about building criteria, including a whole family involving multiple conditions linked with 'And', 'Or', and 'Not', and the behaviour of Null in conditions. Only a few more examples are provided here.

    "ShippedDate Is Null And RequiredDate >= Date()"
    "UnitPrice = 0 Or Quantity = 0"
    "Country In('USA','Canada','Mexico')"
    "ContactTitle = 'Owner' Or ContactTitle Like '*Manager'"
    "(ContactTitle = 'Owner' Or ContactTitle Like '*Manager') And Fax Is Not Null"



Advanced Tricks

The arguments of the functions could have been named after the SQL clauses they represent. The actual query being run by DFind() and DList() is:

SELECT expr  FROM domain   [WHERE criteria]   [ORDER BY order]

A database developer will recognise "domain functions" for really being "dynamic SQL": queries built and executed on the fly. Knowing this, all tricks available in select queries are possible in the arguments.


The result below is not very satisfying: it shows a list of ID numbers

' Products IDs for Order 10401

  ? DList("ProductID", "[Order Details]", "OrderID=10401")

To display a list of product names, the function should call a stored query, linking the tables [Order Details] and Products. However, it can also be done "on the fly". Notice the careful use of table aliases. The tables are renamed 'OD' and 'P', and these letters prefix every mention of a field, in all three arguments.

' Building a multi-table domain argument.

  Domain$  = " [Order Details] OD INNER JOIN Products P ON OD.ProductID = P.ProductID"
  Criteria$ = "OD.OrderID=10401"
  ? DList("P.ProductName", Domain$, Criteria$)

' The same arguments, with different sort orders

  Order$ = "P.ProductName"
  ? DList("P.ProductName", Domain$, Criteria$, Order$)
  Order$ = "OD.UnitPrice * OD.Quantity DESC"
  ? DList("P.ProductName", Domain$, Criteria$, Order$)


The following example uses a different syntax, without intermediate variables. Background: the table [Order Details] contains the archived prices of each Product, at the time of the order. Let's examine these prices:

' Inflation of 'Queso Cabrales' - product 11

  ? DList("DISTINCT Format(UnitPrice,'Standard')", "[Order Details]", "ProductID=11")

And now a related multi-table question, as a single expression. Note the table aliases again.

' When did the price cross the $20 barrier?  --  one argument per line for readability

  ? DFind( _
      "O.OrderDate & ' (' & O.OrderID & ') ' & Format(OD.UnitPrice,'Currency')", _
      "Orders O INNER JOIN [Order Details] OD ON O.OrderID=OD.OrderID", _
      "OD.ProductID = 11 And OD.UnitPrice >= 20.00", _
      "O.OrderDate")


Finally, sub-queries are allowed in the criteria, this is also true for DLookup, so let's see an example producing a list.

' All the supplying countries:

  ? DList("DISTINCT Country", "Suppliers")

' Countries supplying seafood (category 8)

  ? DList("DISTINCT Country", "Suppliers", _
      "SupplierID In(Select SupplierID From Products Where CategoryID=8)")



Technical small print

Functionally, DFind() is almost compatible with DLookup(). Among the differences, there is the detail that table names with invalid characters need to be enclosed in brackets, and the more fundamental fact that they operate in different name spaces.

DLookup() is implemented as a method of the Application object. The benefit is that many things managed by the application become visible, most notably the Forms collection and the controls on the current form, when DLookup is called from that form. For example, a criteria like this is possible within the quotes.

    "CustomerID = Forms!frmOrders!CustomerID"

DFind() on the other hand runs from the DAO name space, within the Visual Basic name space. It can use any VB resource, but the Application's objects are not directly visible. The reference to any Access object should be done outside the quotes, or through Eval(), which creates a somewhat odd syntax.

    "CustomerID = '" & Forms!frmOrders!CustomerID & "'"
    "CustomerID = Eval('Forms!frmOrders!CustomerID')"

This distinction seems rather esoteric. But since users often refer to access objects in queries, this problem will arise each time a DFind() is performed on such a parametric query. For this reason, the domain should accept embedded references to Access objects. The function therefore doesn't open the recordset directly, but creates a QueryDef object instead. Any "missing parameter", candidate for an object reference, is passed to the Eval() function.

Incidentally, this will also evaluate any references to access objects in the arguments expression and criteria. This makes both functions -- the Access version naturally -- very much compatible with DLookup().


On a lighter note, the functions are implemented in DAO. This isn't explicit, and the reference to the library isn't required in the Access version. The "With / End With" derives all objects from "DBEngine", which is an Application method (the interface uses DAO as main engine). Naturally, ADO can also be used to implement the functions. This is tempting, because the ADO Recordset object has a method called GetString, used to generate lists from records.

I posted several versions of list-building functions in questions on this site. In the last three years or so, one generic version stabilised under the name DConcat() -- "domain concatenated list". Here it is, with the argument Order added for compatibility with DList(), so no help page is needed. The GetString method adds an unwanted trailing delimiter, which makes the function a little "unclean", but it also allows multi-field recordsets to be converted to a list in a single call, something you can use to create efficient custom list-building tools.

Function DConcat(Expr As String, Domain As String, _
                          Optional Criteria = Null, Optional Order = Null, _
                          Optional Delim As String = ", ")
                      '
                      ' Similar to DLookup, but returning a concatenated list
                      '
                      On Error Resume Next
                          
                          DConcat = Null
                          With New ADODB.Recordset
                              .Open "Select " & Expr & " From " & Domain _
                                  & " Where " + Criteria & " Order By " + Order _
                                  , CurrentProject.Connection, adOpenDynamic, adLockReadOnly
                              If Not .EOF Then
                                  DConcat = .GetString(RowDelimeter:=Delim)
                                  DConcat = Left(DConcat, Len(DConcat) - Len(Delim))
                              End If
                          End With
                          
                      End Function

Select all Open in new window

There are naturally many versions of function similar to DList() -- or DConcat() -- to be found on the Internet. I would like to point to just one of them: Concatenate values from related records / Allen Browne, June 2008. The arguments are identical in order and meaning. The function does have some problems (for example the handling of errors), but the reason I'm including this link is that the function handles multi-value fields, something I honestly didn't even think of.



Conclusion

[ Tomorrow on Experts-Exchange ]
...
Expert: There is a simple solution: DFind("AppID", "Appointments", "PatientID=101", "AppDate Desc")
...
Expert: To create the list, you can use DList("FullName", "TableWithPlayers", "Sate='TX'")

Every developer has a bag of more or less standard functions, which are carried over from application to application. In the days of Access 2, the number one candidate was Nz() (or n2z, NullZero, NoNull, etc.). When working on an unfamiliar database, I was always puzzled -- for one second -- if a call to Nz() didn't work. I already perceived it as a built-in function.

The functions in this article could easily become like that if you start using them. You will type DList() just as naturally as DSum(), and be startled -- for one second -- when it doesn't work...


I hope you will find them useful and reliable in your applications.


Happy programming!

Markus G Fischer
(°v°)

about unrestricted Access