How to Use DLookup in Microsoft Access

i'm your host sean mckenzie thanks for joining me once again on my channel on data engineering in this episode we return to our microsoft access playlist and we're going to take a look at dlookup which is one of the most useful uh functions that access has built into the product um that once you start developing with access you're gonna use d-look vlookup like everywhere and uh thanks for viewer request on this one because i think that this is a really great topic and while vlookup does have a bunch of brothers and sisters other domain functions that maybe i'll cover in another video dlookup itself is so widely used that it deserves its own video and so without further ado let's get to our dlookup in microsoft access looking for programmers for your project make sure to check out the additional links in the description okay so if you're here you're probably wondering you know what is dlookup and and you know what can i do with it and where can i use it and and when should i use it and when should i not use it and those are the kinds of things that we're going to answer today and dlookup is basically domain lookup which means that it gives you the ability to look up one value in a table somewhere or a query somewhere based off of some criteria that you want just in that moment and so it's very very handy it returns a single value and so if you know that what you're looking for is a single value somewhere then this is very very handy it's a very very handy function to use and it is also it's an expression which means that in the microsoft access world that you can use a dlookup just about anywhere so you can use it on a form you can use it in a query you can you can use it in vba and you use almost identical syntax between all of those contexts and that makes it extremely powerful because you can use it in all of those places and i'm gonna show you how to use those today so in its basic form d lookup is very simple basically you just you know you ask for the value by saying dlookup and then as your arguments you specify what the field name is that you want uh followed by what the table or query name is where you want to get that field from and then the third criteria is what is the criteria that you want to use in order to get that value from that table or query and so it's very very simple very very powerful and we're going to go take a look at that now and this is a file that we've been using for lots of other examples and i've got some candy tables here and i'm going to use this candy table has a bunch of different you know candies with prices associated with them and what i'm going to do is i'm going to create just a blank form in form design so this is an unbound form and so it's not connected to any tables or anything yet and i'm just going to drop i'm going to drop a combo box on there and i'm going to use the wizard this time i'll i'll grab the candy table and i'll just select the candy name and i'm just going to throw that into the fields there and you can see that it automatically puts that id field in that the primary key field the id so we've got to make sure we pay attention to that but this is what the list will look like and when i select this list in my combo i'm going to use that key and then i'm going to use a dlookup to go grab the price for that because that's what i want to show so i'm going to say you know my my label is candy name and then if i click on it here you can see in the property sheet on the right i'm just going to add a name that makes sense so i'll call it cbo candy for combo candy and then you can see that the wizard put in this id field here and it's got one bound call the first column is bound and it has two columns so that means that that first column with the id in it is hidden and so that's a numeric id as opposed to you know the name of the candy so just be aware of that as we do this little demo here and so i'm gonna call this candy price lookup form you know something very simple and now uh you know if i go and i open this now i've got a list on a form of my different candies and you can see some of them are actually duplicated duplicated there but the but the main thing is that we have our list and the id field is in the background so even if the candies have the same name they have an id in the background which differentiates them so the next thing i'm going to do is i'm going to grab a text box here and i'm just gonna throw that on the on to the form and uh we're gonna call it candy price and uh and this is where we're gonna use our d lookup so whenever we choose our candy name in the top there we're going to grab the price for that in and put it into the text box so i'm going ahead i've renamed the text box to txt price so that it actually makes sense and then you can see it has no control source which means that it is unbound and uh and if i uh check that again there you can see the control source is empty and now we can type into the control source on the right or we can just type directly into the field as i'm doing here and this is where we'll put in dlookup and we'll say you know look up the candy price and that that's our first argument and then we'll put a comma and we'll put our candy table name as the second uh argument there and so it's going to get candy price from the candy table and we're going to say where the id is equal to and then we're going to we're going to do a concatenation here using the ampersand and we're going to put in cbo candy and uh that will get the value from the the text box or pardon me the drop down called cbo candy in order to evaluate that d lookup expression so now you can see i i do have a value here if i choose a different one uh you know if i choose a different one here it changes the price each time and that's what we really want to see and so that's one way that you can get that value and and so however if you clear it you can see well that causes an error because the because there is no it's looking for a number so that means that we need to do a little bit of work around um you know if the criteria happens to have a null in there then we need to throw something in there or we need to do some work around that to make sure that we don't get an error there and i'll show you how to do that in a minute here but first you know you can just hard code a value into so if i go into the control source here and i go down to our concatenated expression i can actually just remove what we put in there that was dynamic and just put in a hard coded number like maybe you just want this form to always show the value of a particular thing so you might just hard code it and in in this case if i change it it doesn't do anything because the the bound expression there is for id number 14 so it's just going to get that one particular price so you might be asking well how can i deal with that situation where you know it gives an error and in this case there's many ways that you could do this [Music] and this was this is just one way to do it using vba but what we'll do is we'll we'll remove the expression from the candy price field and i'm going to leave that empty but now we're going to look at the candy name or pardon me the candy drop down and we're going to go to the after update event we're going to click on the ellipsis and then that's going to give us a vba procedure where we can you know basically just go ahead and do a vba procedure here we'll say you know we can just say if it is null the candy drop down that's you know when it's empty then uh you know me a text price that text box for price is null so just set it to empty and if the uh otherwise get the value from there and put it into our dlookup and and then that will you know allow us to get that value so we use the same exactly the same syntax in vba we use dlookup you know candy price candy as the table and then id is equal to and in this case it's slightly different you put me exclamation cbo candy you can actually just put in cbo candy without the me in there as well some people like to do it that way [Music] but now if we open up our form and we change the value and you can see now it's changing the value for the prices but if we you know if we go ahead and get rid of that value that's in there instead of grabbing a value then it's going to give us an empty so i'll erase that and that gives us an empty price down below and it does not give an error like we saw before so that covers our forms and reports reports are almost identical in the usage so you can you know use dlookup in your reports as well and i'm going to create a query here from the create ribbon there and and i'm going to i'll first i'll show you this table this has a list of new prices for particular candies and so this is a list you know similar to what we saw before but this has new prices on it and the the ids don't match so this is in this case we're gonna use only the candy name and it's gonna show you the candy price so here's our our new prices for candy and so what i'm going to do is i'm going to change the name of the output field for candy price i'm going to call that the new price and then i'll in order to get the old price for candy in my query i can use a d look up and uh and i'm gonna use that here so i'm gonna say d look up candy price um from the candy table and where you know the candy names match basically and now in this case i i need to be aware that um in the other table if you recall there were multiple candies with the same name even though they had different ids in the background so this is actually going to grab a random a random price from the ones that have duplicates it's going to grab one of the prices that were in the other table so depending on your circumstance you might need things to match exactly and and get that but in this case maybe we just want to grab one of the old prices of the candies in the other table and so now if i open the the query you can see that here's the old price from the other table and you'll you can actually see it's not formatted because it's just grabbing the numeric value whereas the we can see the formatting in the candy the new new candy prices but as you can see there's the dark toffee 4157 is the old price and then the new price is 36 and so that's sort of like how you can stick a dlookup into a query which is really really handy but one of the gotchas is that like i said before you need to make sure that you're calling something by an id value or something that you know is relatively unique otherwise you are going to get like one it'll choose one for you basically randomly of the multiple values that could come back and so the better that you know the values uh the better that you know the criteria is going to return that value that you want it's it's the better result you're gonna have okay so that covers the you know the random value return uh gotcha now the second gotcha that you have with uh dlookups and actually domain expressions in in general is that they are very slow if you have to do a lot of them and so much in the same way that sub queries that are used in sql if you use them the wrong way they'll slow down your query domain lookups are almost exactly the same so that means you know if you have a hundred records or you know 50 records and you put a d look up in there to get something in your query then that's fine that's probably going to work great if you have hundreds of thousands of records or more you really should not use dlookup and make sure to check out my video on uh the simulating lag and lead where i show uh how just how slow domain functions can be inst as opposed to using a proper set operation which we call where you design actually design a query properly so that it will be optimized and return the record set in a fraction of the time that you know a query with a ton of dlookups in it will return the same and so if you're troubleshooting a report or a query that is very very slow uh looking for d lookups is probably one of the first things that you should do because if you find that there are a bunch of d lookups you can convert those into set operations and it will speed up your report immensely need help for coaching on your project make sure to check out my patreon the link is in the description hope you enjoyed today's discussion on dlookup in microsoft access if you like what you saw today please give the video a thumbs up make sure to subscribe to the channel if you didn't subscribe yet click the bell when you see the bell and if you have any questions or comments put those in the comment section below have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 321

Keywords: how to use dlookup in microsoft access, dlookup, access dlookup, ms access dlookup, domain lookup ms access, dlookup on form, dlookup in query, microsoft access tutorial, vba dlookup, dlookup multiple criteria, dlookup access, dlookup function, ms access, sean mackenzie data engineering

Id: ln-Mkb5zstE

Length: 15min 47sec (947 seconds)

Published: Tue Nov 30 2021