<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Dlookup and the Domain Functions

Published on
32,636 Points
12,536 Views
16 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
Jim Dettman (Microsoft MVP/ EE MVE)
Independent consultant specializing in the writing of custom packages for businesses.
Applies to: All versions of Access   Skill Level:Beginner

  One of the common questions we see in the Access zone is how to get a value from a table.  One way to do that is with a set of built-in functions called Domain functions.  These functions work against a set of records (a domain).  Most of these functions perform some type of aggregate operation (Sum, Count, Avg, etc).  The domain functions are DAvg, DCount, DLookup, DFirst, DLast, DMax, DMin, DStdev, DStdevp, DSum, DVar, and DVarp.  You can find specifics on each of these in the on-line help.

 With all of these functions you can carry out the same logic (i.e. summing a value for a given field) yourself by opening a recordset, scanning through the records, and analyzing them appropriately as you go or by executing a SQL statement (a query).  In some cases it makes sense to do this, but it is a lot of extra work in terms of coding.  The beauty of the domain functions is that they are so simple to use.  They wrap up commonly needed SQL logic in a neat easy to use function.  What is also nice is that they can be used anywhere an expression is allowed (where Access expects a value).  For example you can use a DLookup() for the controlsource of a text control on a form.

  One popular myth in the Access world is that domain functions are always slower then other methods.  This is not true.  In some cases, the domain functions can be as fast or faster then other methods.  If performance is a consideration, then you should test both for the specific situation and see which is better.  But if you ignore domain functions because of this myth, you are only cheating yourself because time is money and you could be wasting a lot of it.

  Of all the domain functions one of the handiest is Dlookup, which returns a value from a field in a record.  Often on a form or report or during the middle of a procedure, you need to get a single value or a couple of values from a record source.  Dlookup is perfect for this.  Keep in mind though that for each call to Dlookup, you are:

  Opening a recordsource
  Searching for a record
  Returning a single value

 So if you need more then a few values from the same record, you should not use Dlookup.  Instead open a recordset and find the record you need.  At that point, you will have all the field values at your disposal without any additional overhead.  Another place where a domain function should not be used is inside of a query.  The reason is that the functionally of the domain functions are already at your disposal through the use of SQL and the joining of tables.  When you add a domain function to a query, you are in affect creating what is called a sub select; that is a SQL Statement that is executed for each row of your main select.  The problem is that unlike a sub-select statement that you write, a domain function cannot be optimized by the query parser, so you will get poor performance.

If you lookup Dlookup in the on-line help, you will see that it has the following syntax:

   = Dlookup(<Field Name>,<Record Scource Name>,<Where Clause>)

==============================================================
TIP:  Even though the above syntax shows that commas are required between the arguments, in rare cases you may find that this gives you a syntax error.  In those cases, try using a semi-colon (;) instead.
 ==============================================================

  Since Dlookup returns a value (as do all of the domain functions), it can be used anywhere in Access an expression is allowed.  That means you can use it in a macro, as the control source of a control on a form or report, or in code.  So let's look at a practical example.  In my database, which I use for multiple companies, I may have the following table, which has one record in it for each company's information:

tblCompaines  - One record per company
CompanyID - PK (Primary Key) - Autonumber
Name
Address1
Address2
City
State
Zip
Phone

  Within my database, I wish to place the company name that I am currently using at the top of every form or report that I open.  To do this, I would place a text control on the form or report, and set its control source to:

 =Dlookup("[Name]","[tblCompanies]","[CompanyID] =  " & gCurrentCompany)


  What the heck is all that?  Let's take it a piece at a time.  Looking back at the required syntax from the on-line help we see that the first argument is the field name that we want returned.  The on-line help also indicates that the argument needs to be a string value (surrounded in quotes) rather then a numeric one.  Well that is simple enough then.

  Looking back at the table, we want the company's name returned, so it is "[Name]".  But you will notice that I have also added a set of [] (square brackets) to the field name; what is up with that?  Well mainly it is a good habit to get into.  In Access, you must use square brackets anytime you have an embedded space in a object name.  So something like "First Name" must always be referenced as "[First Name]" or Access will only see "First" and then give you an error.  But even if the field does not have an embedded space, it is nice to know just by looking at something that you are referring to an object, so it is something you should do all the time.

==============================================================
TIP: While it was just said that object names can contain an embedded space, it is a bad idea to do so!  Why?   Because you can save yourself a lot of time debugging (and the resulting loss of hair) when you do not have to figure out that "[First Name]" is not the same as "[First  Name]".  Did you spot the extra space?  Depending on the font size and type you are working with, it can be extremely difficult to determine if you have one or two spaces in a object name.  This applies to any type of object (fields, controls, forms, reports, tables, etc) that you name.  When naming objects, make sure too that you avoid using any reserved words (anything that Access uses itself like "Date", "Select", "Form", etc).  Last, it's a very good idea to use a naming convention.  It really doesn't matter which one (even if it's your own), just that you use one.  One of the most popular is the Leszynski/Reddick naming convention.  By doing all of the above, it will make your app more readable and easier to maintain latter on.
==============================================================

  Now that Access knows what we want returned, it needs to know where to look.  That is the second argument, which is the data source that it will look through.  In our example it is the company table, so the second argument is "[tblCompany]".  Note that Dlookup is not just limited to tables, it can work with queries too!

  Last, how does Access know which record to look at to get the value to return? Remember we are looking at a domain or set of records, so there may be one or more records for it to choose from.  The third argument determines that.  It is a SQL (Structured Query Language) WHERE clause without the word WHERE in front of it.  Come again?  An SQL WHERE clause is used to filter a set of records.  Only those records which meet the criteria specified are looked at.  The rest are ignored.  In the example I gave above, we were looking to get the company name for the current company.  In this database application, the company number was selected at login and stored in a global variable (a variable which is available for as long as the app is running) called gCurrentCompany, which is currently 1.  So we want our third argument to end up like this:

[CompanyID] =  1

  Since we are using a variable to store the current company, our actual syntax for the third argument will be:

"[CompanyID] = " & gCurrentCompany

  When Access looks at that argument, it will strip off the outer pair of quotes for each value it sees (this is the first thing Access does whenever a string is passed as an argument) and then get the value of gCurrentCompany.  So we end up with:

   [CompanyID] =  1

  which is what we want as a end result.

  Now, when looking through the records in tblCompanies, Dlookup will look only at those records where the CompanyID field equals 1.  For this example, there should be only one record that matches, and it is that record's Name field that will be returned for the value by Dlookup().  What if no records in the record source meet the criteria?  Dlookup() will return a Null.  A Null value is not the same thing as a 0 (for a numeric) or an empty string (for a character value).  Both of those are values.  A Null is the absence of any value.  What if more then one record in the record source meets the criteria?  Dlookup will return the value from the first record that it encounters that meets the criteria.

  There is one more "gotcha" with Dlookup and it involves the WHERE argument (and the passing of a sting within a string in general).  Instead of the company table above, lets say that we had:

  tblCompaines - One record per company
  CompanyID - PK (Primary Key) - Text
  Name
  ...

  Notice that CompanyID is now a Text field.  In a SQL WHERE clause, text (string) values need to be surrounded with a delimiter (a marker), which is normally the quote character.  You can also use a apostrophe (') or single quote. So what Access needs to see is this:

  [CompanyID] = "ABC"

  or this

  [CompanyID] = 'ABC'

  as a finial result.  The way we have our third argument though is:

"[CompanyID] = " & gstrCurrentCompany

  and as a result, Access will end up with this:

  [CompanyID] = ABC

  An error results because we need quotes around the value ABC to indicate that it is a string.  Without the delimiters, Access will treat it as a number and ABC is certainly not a valid number!  The problem occurs because we are passing a string inside of another string.  One way to get around the problem is to use additional quotes.  We can do this because of the fact that with special characters, Access will treat two consecutive characters as a single character (this is so you can use characters which are normally special in some way inside of a string as an actual character).  So starting off with this:

"[CompanyID] =  """ & gstrCurrentCompany  & """"

  ends up as this:

   [CompanyID] = "ABC"

Here is how that works (remember for each string value that is passed in, Access strips off the outer pair of quotes):

  Start with:
   "[CompanyID] = """ & gstrCurrentCompany  & """"

  and strip off the outer pair
   [CompanyID] = """ &  ABC & """

  and now each double is treated as a single quote:
   [CompanyID] = "" & ABC & ""

  and once again, we still have some double quotes, so they get removed:
   [CompanyID] = " & ABC & "
 
 which gets us to:
   [CompanyID] =  "ABC"

  Pretty confusing keeping all those quotes straight!  It also makes it difficult to read (especially when you get longer WHERE clauses), which becomes a debugging nightmare.  So you might be tempted to do this, which is to use an apostrophe (single quote) in place of some of the quotes:

  "[CompanyID] =  '" & gstrCurrentCompany  & "'"

  Again a bit difficult to read, but it is still better then all those quotes!  However there is a problem with this.  What if the string has an apostrophe in it?  Lets say the company ID is D'1 (this would more typically occur in something like a name, such as D'Amato, but we are using company ID in the example, so we will stick with it).  Access will end up with:

  [CompanyID] = 'D'1'

  Which is not good as you will get an error due to the unbalanced quotes.  So what is the solution?  There are two.  One inserts a quote without using the quote character itself:

  "[CompanyID] = " & Chr$(34) &  gstrCurrentCompany  & Chr$(34)

 The Chr$(34) is a built-in function that produces a character by using its ASCII value.  A 34 as the argument gives you a quote (").  As a result Access does not see a quote directly and parses (breaks down) the argument correctly.

  The other technique is to use a constant (a pre-defined value) to hold a set of quotes.  In the top of a module, which is the declaration section (things defined here are available for the life of the app once a module is used), you would define something like this:

  Const  Quote = """"

  Then your argument would look like this:
   "[CompanyID] =  " & Quote & gstrCurrentCompany  & Quote


Whew<g>.  If all that makes your head spin, then join the club!  What it boils down to is that you should always use one of the following methods when you pass a string within a string:

"[CompanyID] =  " & Chr$(34) &  gCurrentCompany  & Chr$(34)

OR

  Const  Quote = """"
  "[CompanyID] = " & Quote & gCurrentCompany  & Quote

  I hope you found this article useful.  If you have not used Dloopkup() (and the other Domain functions) before, or have been ignoring them, give them a try.  You will be surprised at how easy and painless it is to use them.

Jim Dettman

Comments, corrections, etc. about this or any other article are welcome!
16
Comment
9 Comments
 

Administrative Comment

by:Netminder
Marked as EE-Approved.

Outstanding work, JDettman!

Netminder
Site Admin
0
LVL 61

Expert Comment

by:Kevin Cross
Nice one, Jim.
Voted yes above.
0
LVL 18

Expert Comment

by:WaterStreet
Very nice article.
I found it rich in lot of things that I should have known long ago.
I voted yes above.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

LVL 18

Expert Comment

by:WaterStreet
Very helpful.
I voted yes above
0

Expert Comment

by:okacs

What is the difference between Dfirst() adn Dlookup() ?  In testing against 1:many data-sets we find them functionally equivalent.    After researching them on the MSDN and Support.MS sites, we are still confused as those texts do not make the distinction.

per Support.microsoft.com:  DFirst() may return unexpected results when used to find the first item in a list.
 http://support.microsoft.com/kb/105519

You can use the DFirst and DLast functions to return a *random* record from a particular field in a table or query when you simply need any value from that field.
 http://msdn.microsoft.com/en-us/library/aa159042%28office.10%29.aspx

The DLookup() function returns one value from a single field, even if more than one record satisfies the criteria. If no record satisfies the criteria or if the domain contains no records, DLookup() returns a Null.
 http://support.microsoft.com/kb/285866

Can you clarify this for us?
0
LVL 75
DLookup is specific ... it does exactly what you specify it to do.

DFirst() and DLast() are a bit misleading and confusing as you can see the the Access Help and KB's.

They are not functionally equivalent.

mx
0
LVL 60

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
<<What is the difference between Dfirst() adn Dlookup() ?  Can you clarify this for us?>>

  DFirst() and DLast() ignore any indexes and order bys; literially they give you the first and last record of a table according to the way records were entered.
 
  DLast() is really bad in that it will run through the entire table to figure out what record was entered last.

  Dlookup() however *does* use indexes and will honor order by clauses.

JimD.
0

Expert Comment

by:STERLING SUTTON
Best explanation I've found yet. Thank you for clarifying the quotes in Domain Aggregate functions.

I'm currently fighting with a DLookup function with criteria that references a variable of date type. I can't get it to work. How do I get it to recognize the date? It isn't a string. I tried putting the field in quotes, but that made the comparison completely unused.

"[Expiration_Date]=" & [Expiration_Date]

Got it.

"[Expiration_Date]=#" & [Expiration_Date] & "#"
0
LVL 60

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
<<"[Expiration_Date]=#" & [Expiration_Date] & "#">>

  Nice...you figured it out.   And glad you enjoyed the article.

Jim.
0

Featured Post

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Join & Write a Comment

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month