Access 2013 15 - Dlookup Function

hello and welcome to Access 2013 tutorials over the next couple of videos I'm going to be attempting to create a sort of multi-purpose query that we can use as the basis of reports and also as the basis of form and it's going to be related to booking details now in order to do this I'm going to need to use a couple of different techniques and a couple of different functions and in this video I'm going to be explaining to you the D lookup function and how you can use that in a query so if you want to play along follow the link below the video to the download section a website and download access 2013 DB 15 and then we open that one up okay so what I'm interested in is if we take a look at table booking details this is where we keep most of the useful information about booking details funny enough including the check-in day the checkout they the room and obviously some notes and we're going to be creating a query that's kind of based off of this doing it bit by bit so like I said this particular video we're going to be focusing on a dealer cup function so if you go to the create tab and click on query design and for the moment just pick table booking details and add and we can pretty much add all the information in there so what we've done is just replicate the original table but what I want to do I'm going to use a dealer cup function for this is I want a column that indicates the cost per night of a room and there's a couple of ways of doing this I could actually just drop the room table in there and use the cost per night then but then I wouldn't get to show you the dealer cup function so that's no good is it so what we do have is a room ID which is here room idfk and what I want to do is use this room ID to go over to the room table table rooms and to look up the cost per night for that particular room ID that's why I want to do and we use the dealer cup function to do that so we skirt along here and we click in to this empty column click on builder and what we're going to call this column well we'll call it cost per night and put a colon so this is going to be the name of that particular column and now this is going to be the data that it's going to hold and D lookup click on that so the dealer cup function takes two required arguments and one optional argument the first required argument is what field are we looking for and I don't remember so close that we should leave this open actually so the field we wanna actually return is called cost per night I could probably guessed that birth why take a chance eh okay so it will cost per night so it needs to be within open and closed quotation marks and also it's a good idea to put it within open and close square brackets so it looks like this okay so that's the field we're returning the next then what we do a comma and we say where it's coming from this can be the name of a table or a query in this case we're just interested in table rooms and now we get to the optional part which is where we specify some kind of criteria the criteria is the room ID has to equal whatever the room ID returned by room idfk is now on every single record I'm going to run this I'll put it to datasheet view and please ignore what's in ignore that for one second very single record here we have a different well not for every single record but for gompa records one until we have a different room ID so when we're working on the cost per night we want to work out based on this room ID and here we want to work out based on this room ID so what we do is we go back to a dealer cup function and we've put another comma and open and closed quotation marks over close square brackets and it's room ID equals now we're going to use an ampersand and room C as it is okay room ID SK which should actually probably be it open or close square brackets anyway okay so press okay and get it run it actually now we get the different cost per night just trying to explain that last bit to you we've actually done is the room idfk refers to this field here remind EF k so if for example within the record that's number one it will literally say look up the cost per lying table rooms where the room ID is number one and if that's number two say you look up the cost per nine table rooms where room ID is number two so that's dlookup function is one other thing we can do because at the moment is just returning a number and I'd like it to return it like a currency we can actually convert this to currency by typing in here CCU our racket and that means convert currency so anything within CCU are brackets will be converted to a currency and we end up with that okay we'll save this and we'll call this query booking details X then did that's okay and there we go thank you for watching and I shall see you in the next video

Channel: Access All In One

Views: 92,400

Rating: 4.6331878 out of 5

Keywords: Microsoft Access (Software), MS Access Tutorial, microsoft access tutorial, What is MS Access, What is MS Access used for, Access 2013 Tutorial, MS Access, Access, Access Database tutorial, Training, Tutorial, Software, Download, How-To, Class, Classes, Course, Microsoft, MS, Tips, Help, Programs, Educational, Relational Database, Dlookup, Dlookup Function, Queries

Id: l6ZywZikcPI

Length: 5min 58sec (358 seconds)

Published: Tue Jun 11 2013