The DLookup Function returns a value of a particular field from a specified set of records. The DLookup Function has two required arguments and one optional argument. <<expression>> represents the field that contains the data you want returned. <<domain>> is the name of the domain, mainly a table or a query, and <<criteria>> is the optional argument that is used to restrict the range of data. Now, writing criteria for any domain aggregate function not just the DLookup Function can get a bit complicated. If you want criteria for a particular field to be equal to a text string— say the name “Smith”— here's how you can write it with double quotes surrounding the criteria expression and single quotes surrounding the literal strand that is if you know the name of the particular customer you're looking for and if you want that particular customer returned. That's how you'd write the criteria. But, if you want your criteria to be a bit more flexible or, in other words, equal to a particular field on a current form or report, you have to write your criteria like this. To explain what exactly you were looking at, think of this expression as being in three pieces or parts. So when this criteria is evaluated first the individual pieces are evaluated and their results concatenated. After that, then the whole value is computed. So if we were to evaluate the individual pieces and then bring that together, this is what we're left with: the simple criteria of CustomerID = 7 Now, I should warn you at this point, if you miss-type or write the aggregate criteria and with that criteria there's a lot that you can leave out or mess-up. If you do make a mistake in the syntax for the criteria, Access won’t evaluate the criteria, which means it will think there is no criteria and perform the function as if you didn't write any criteria at all. So when using a domain aggregate function really scrutinize the returned result, maybe even trying out different criteria just to be sure the function is working as you intended— just a little warning there. And we're back to rptAverageBooksSoldPerDay which is the [Microsoft Access] report we've been using for the last few exercises. This time, at the bottom, next to our Highest Average Sales and our Lowest Average Sales—those numbers— Let's say we actually want to spit back the name of the books for these numbers. So going back into Design View, we’re going to add a few more controls. So I'm going to move over my labels just a little bit click on that control and click once deleting the label clicking again, again deleting the label (we only need one) And for this I am definitely going to open up my Expression Builder 'cause I am going to need a lot more room to write this out. Let's open up our built-in functions folder go all the way down to domain aggregate and then double-click on the DLookup Function to send it up to our Expression Builder. And once again, <<expression>> is asking for the name of the field we want returned. So in this case it's going to be, in quotation marks, our BookTitle field. The domain is going to be the name of the query or the domain that we are pulling information from or out of, and in quotation marks, I’m going to type “qryAverageBooksSoldPerDay” close quotes comma and then for our criteria in quotation marks we want to have the number, the average sales per day, equal to the number that’s displaying on this particular report. So, we need to write the reference for a field within qryAverageBooksSoldPerDay, so…let's write that out in brackets: “[AverageSalesPerDay]”, close bracket. And that's going to be equal to… now we have to split up the criteria, so this is the first part…so let's have a single quote because we want that as part of the first part of what's evaluated, and then type close double quotes to close that section. Now, we’re going to take the information that’s evaluated from there, and join that with information that appears on this particular report, or this object, So the way to write a reference for something within an object like a report is to write out a very long reference. In this case, we're gonna tell Access ‘hey, look in our reports and you can see that Intellisense is telling us a list of everything that begins with an ‘r’ we want reports followed by an exclamation point so in that report section we've got two reports to choose from, rptAverageBooksSoldPerDay, that's the one we want press center to have Access write that out for you, then press exclamation point and here are all of the available controls that we can pull from. And we want the one that is called [MaxQTY]. And if you notice Intellisense didn't grab that field for us as we were typing and that actually is a sign that that field does not exist yet, and I’ll explain that in a minute Let's go ahead and finish this function. So this is going to be our reference to a field on this particular report which we're going to join, so type out that ampersand, with a single quote, so we want that literal single quote to appear, so we've got to type a set of double quotes, single quote, and then another set of double quotes. So, that's what our expression looks like. Let’s go ahead and click OK. It's not going to work just yet because we haven't actually named this field what we need to name it. So, this one once again, is referring to a field [MaxQTY], well, that's what we need the name this field so it knows what field we're talking about So let’s select that property, or that control, and go up to our Property Sheet and name it MaxQTY So now this is referring to this particular field, and if we jump back into our Report View and scroll down it should return the name, “The Potion and the Scroll” so the DLookup function is spitting back the book title that appears in the object qryAverageBooksSoldPerDay where the field within that object, or that query, qryAverageSalesPerDay, is equal to the number that is appearing on this field, on this report. And that's what we just told access to do. Now let's do the exact same thing for the lowest average sales. Let’s go back into the Design View, and let's uh... first rename this field so we can reference it easily in another function so selecting that… clicking on the Property Sheet and going to the Other tab instead of Text15, let's call this something a little bit more meaningful— let's call it MinQTY and close and now we're going to do that all again. In fact, to save us some time let's just copy and paste this formula here and then tweak it our Expression Builder. So, the only thing they we’re really changing here—this stays the same, this stays the same, that stays the same— is the field that we are comparing it to, which is going to be MinQTY, and see how it shows up now… that's because we now have a control named that on our report. There it is…oops… and that's what we're changing—go ahead and click OK and jump back into our Report View and scroll down. It should return Picaroon Quarterly 2.4. And those are two examples of how to use the DLookup Function in an Access report.
