32/50 - Macro Dlookup Functions - Microsoft Access 2010 Tutorial

this lesson we can be looking to D function specifically the dlookup function and the last function okay so if you've been following along with the databases please open yours up if not go to the link below the video go to the download section a website and download DV video 32 and then open it up in all its glory you probably if you've done that will get a bar up here saying enable content just get rid of the log in modified box enable content and it will bring that login box up again and you can log in all well and good right so what we're going to do what we're going to do well if you have been following along with the databases you'll know that we are trying to use this form here which will form Junction student courses to add a course for a given student so for example the student here this list box lists this course issues attended always attend in this list the courses you can potentially attend as we click on there we can throw these over here and the ID then being to add that to table Junction student courses in order to do that we're going to need to set some temporary variables and in order to do that we're going to need to use some dealer cups so how we going to do that well first of all let's go to layout view this form and we're going to add a button here and it's going to be called button it's just legal test the reason we're going to have that bun is if we're going to make D lookup functions and D loss function is really useful to have an easily placed button where you can actually test that they're doing what they supposed to be doing now let's go to create and macro and it's create a macro called save it macro functions okay and we're going to put some sub macros in here so it's happening submacro and all the sub macro is it's basically another macro but it's the the reason sub macros exist is the matrix or your macro database a little bit tidier so you can just access one macro macro functions they have dot and then it will list the sub macros it just tidied everything up and keeps everything in its relevant place um so let's just first of all call this one set what we want student and then cool okay and this isn't going to be necessarily relevant to this but this is just to demonstrate dlookup function and what we're going to want to do is we're going to want to set the student name and how we're going to do that is we're going to feed a student ID in and it's going to look up from table students which is down in one or two student IDs let's go with ten Roland Walker and it's going to give us that a last name to whacker so how do we do that well we're going to use that dlookup function okay so I also want it to actually come up in a message so I've got a message box okay go equals let's click on here and D lookup so here's start the function now if you just hit comma quickly you can see that the D lookup function you've got basically three arguments you've got the expression domain and the criteria and a criteria arguments actually optional but obviously that's actually what that's the student ID we're going to be looking up so in our case it's going to be necessary the domain is the table that's holding information and expression is what we're returning so what are we returning we're returning the student last name just go to student L students and we just need to accurately look up the name of that column which is last name no space no underscore so it's D lookup and it's open and close quotation marks open and close square brackets and it's last name okay then we go comma as we hit comma you'll see that the bolt goes from expression to domains it tells us that that's what we're writing so now we need to enter the domain and we can see it's table students okay and now we're going to go to criteria again just go back to table student so student ID we're going to say what is the student ID b1 actually it's 10 isn't it so criteria open flows quotation marks open and close square brackets and in the square bracket puts you and ID just go past the square bracket equals and it was 10 with a net 10 okay and that I want don't forget obviously the closing bracket so we've got D lookup we're looking at the last name from table students and the criteria is student ID equals 10 so save that's a set student name forget it form drunks course new click in your button then unclick macro builder and go run macro now you're going to see that yes Scott macro function has got macro functions dot sets to your name if you tap macro functions you'll just start from the top of run every sub macro in there which is not what we want save that and hopefully this should give the last name whacker brilliant and just to prove the point number 11 is an unpronounceable Krishna so let's change that to 11 save that now if we do it again we get Krishna and that's dealer cup function and what about addy last function well why don't we go to table invoices and what we want is this invoice number here we want to find out the last entry the last entry was 80 and that's what actually the last function does funny enough returns the last ended value in a table so back to micro functions let's make another submacro drag it over and call it set invoice number and again message equals D last we're going D last that's not how you feel D last the last everyone open close protection marks open and close square brackets nose invoice number and the domain is table in voices and in this case it's not really necessary to have a criteria so don't put the bracket I've done for the commissary close up the bracket we don't need a criteria because the criteria sent to be the last record so this should hopefully reveal 18 so I go back to form student pause no layout view and just change this to set invoice number and that's 80 okay yeah a similar kind of function to that one actually would be the d-max function which performs a very similar assess action on so it doesn't force him a role tool in this case it will return the same result but it doesn't actually return the maximum value in a sequence well but why don't I use the West and I used D max why did it do that mmmm okay the reason it's done that it is actually returning the maximum value you have to take my word for that this here in which number if we go to design view you can see is a text field and when you're reading off a text fields it see it literally reads the first finds a max value of the first number or the first character I should say then goes the second one so it sees the nine as greater than any of these ones it doesn't read it in any kind of sequence so the d max doesn't it's really tricky to use when you want to do it on a text field how I could make that work correctly is to change the to put zero to if I did do it is a three all right easier nine you would actually get the correct result returned so there you go there's a D max I'll tell you what I'll do I'll give a I put the the D max on invoice idea then it will actually return the correct 18 but yeah D max not particularly great on text field bit of a pain really there okay so we actually do want to be number so English number has changed up to last so we are just took another last record now there's actually a number of other D functions what else we got the first but you probably work out for yourself the minimum D standard deviation D some again a lot of these you can actually work out loud let's do D some just for the head of it that's some the the invoice IDs why not a kicks and giggles these um so it's going to add all the invoice ID got 164 if you add em all together that's of actually no use to me whatsoever alright today your d lookups now let's make this relevant to the to the actual database we're trying to right now we're going to do that is we're going to change this one to set course ID and what going to want to do is read first course ID from table course temp we're selected it was true in this case it's course ID five so it's going to go to table course temp and the criteria is going to be selected equals true and it'll work its way down to the finds the first record and it's going to return course ID five so dlookup where we what's the expression we're looking for and its course ID we put another way we're looking at course for course ID from table this open up navigation pane from where from table course temp where select it equals true and this should return the value plus tip number five 164 that's because I haven't adjusted the macro so let us do that set course ID yup yeah five so it's done that so that's good and let's just now we've got it in a message box so what we want actually do is go to set temporary variable variable course ID and we're going to use the look up here you'll see I get rid of the message box and and we're going to do a similar thing with set invoice number we want D last invoice number and we're going to be this is copied actually Josie we're going to be setting temporary variable which is going to be variable in voice number and it's going to be control V because I understand the macro format but you're not control c lv + 1 I forgot say invoice number we've got sick course ID and let's stick a message box at the end might start azmuth as I decided to do it I won't do it this line the sub macro just put in a message box and this is double check that it's read D invoice number all right message equals temp variables exclamation mark very rule in voice number and it should actually return nineteen if I spell it correctly save that we're going to change it on last five nice okay so that's it for this video we've looked at the dealer cup function we've looked at the last function the max function don't use that with text fields as we found out and that's basically if you're going to use these kind of functions Bob best bit of advice I can give you do make like us or test button or you can actually check that the values they're giving you a correct as you go along otherwise if you if you for example need to look up a value and then you're calculating some ik based on that value later on all that's going to happen is if you don't know this returning the right value you're going to find yourself having a problem later on the equation and not knowing the origins of that problem so make that test button use it extensively and you'll be absolutely fine okay thanks for watching see you next video

Channel: Access All In One

Views: 25,820

Rating: 4.7288136 out of 5

Keywords: MS Access 2010, Microsoft Access 2010, Access, Access 2010, Dlookup Access 2010, Dlookup, Dlookup Function, Dlookup Access, Microsoft Access 2010 Tutorial, Access 2010 Tutorial

Id: BuTu3ADg-uE

Length: 14min 9sec (849 seconds)

Published: Sun Aug 12 2012