Today is part two of my video series on who had an item on a specific date in your Microsoft Access database. Okay, so yesterday we set up our little product table, so we got a list of products. We set up our rental table, so we know who rented what product on what dates. And then we set up on our form what date we're looking for, pick the product, and then we click the button, and it'll tell us who it is. It won't just say "Hello World." So that's what we're gonna do today: write the code that goes in this button.

So, the logic behind our code is going to be with a DLookup. We're gonna say, okay, here's the product ID and here's the date. Okay, I want to go into the rental table, and I want to say find the rental ID where the start date is less than the date I'm looking up, and the end date is greater than the date I'm looking up or equal to'greater than or equal to, of course, right. So if I'm looking for product 3 on January 7th, it should come back with that record. And of course, there are some options too. If it doesn't come back with a record, that means someone might not have had that product out. Or, we might have a third case where it matches the start date, but there is no end date. So that product, you know, is still out. Okay, so we'll take a look at all these different situations.

So let's go into our VB editor here, right-click, build event. All right, so the first thing I'm going to do is I'm going to look to make sure that they put data in those boxes. I don't want errors. So if IsNull(lookupDate), then status, "Missing date,"; Exit Sub. The colon means I can put another line there without having to do a whole if-then statement. And we'll do the same thing for the product combo. Product combo, "Missing product." So if they don't put anything in both of those boxes, it yells at them. And you can do more stuff. Like, you can move the focus to the product combo box and hit the drop-down action. There's all kinds of stuff you can do. Again, we're keeping it simple, so we can focus on the logic.

All right, so case one is going to be the product has a start date and an end date. Okay, so we'll take a look at that first. Now, what we're going to do is we're going to look up a customer ID from that table. All right, we've got our rental table. We'll just return, we'll just look up the customer ID. And we'll use Null Zero NZ, so that if there is no record, if it doesn't hit something, it's going to return a zero. Yeah, I probably should have listed this in the prerequisites last week, but I forgot. Okay, so if you've never used the NZ function before, all it does is if a function returns a null value because DLookup can't find the record, it just gives you whatever you want, like a zero.

All right, so we're going to need a customer ID variable in here. So Dim customerID as Long. And here we're going to say customerID equals whatever product is in the product combo box. You want to put that outside the quotes, so that it actually gets the value of the product combo box and puts it inside your string, okay? Next line, and some other stuff.

Okay, so and the start date is less than or equal to, now I want to put the lookup date here, but again dates have to be inside of these things, all right? They have to be inside of hashtags, or octothorps, or pound signs, whatever you want to call them, all right? lookupDate, and that will close the date. Okay, and one more thing, the end date has to be greater than or equal to whatever the lookup date is. All right, comma zero for our NZ, and there's your lookup.

I know it's pretty complicated'let's break it down. So we're looking inside rentalT for this particular product, right, productID equals 6, for example, and uh... the start date of the rental has to be less than or equal to the lookup date, and uh... the end date of the rental has to be greater than or equal to the lookup date. So, if I look up January 1st for this product, it should return what I'm looking for. Don't forget your spacing'that's very important, right because you've got the product combo number is going to go right here inside the string. Okay, start date, you've got your dates, right, end date, you've got your dates. And then if this DLOOKUP returns a null value, in other words, there is no customer, then the NZ function will convert that to a zero, and we can use that.

All right, so now the next thing is going to be if customerID equals zero, then status: "No one has it." Okay, otherwise we have a customer, and then you put customerID in here, "Has it," or "Had it." Okay. And yeah, you could do another DLookup in here and DLookup the customer's name if you want to say Joe Smith had it. That's another easy DLookup. You should know how to do that. OK?

All right, let's save it. Debug, compile. Let's give it a test. Let's come back over here. All right, let me just slide this down so we can do some testing here. We want to see our data while we're testing it. I'll move this over this way. Okay, let's say we're searching for, we don't have records for March yet, let's say we're searching for January 7th, and I'm searching for product 3. Oh, what's product 3? Product 3 is that comm badge. Let's pick the comm badge and hit Look Up Rental. "Customer one had that," that's correct, it looked up for that product between those two dates, and it found customer one. Let's see who had the phaser rifle'no one had it, phaser. And let's see who had it on January 7th. No one had it, because, right, it wasn't rented until the 15th. Let's change our date to the 15th. And there we go, "Customer six had it." See? Okay, so that's working so far.

There's one more situation we have to take into consideration, though, and that's what if the product is still out. So let's change this to 1/15. Let's do product 4, which is the tricorder, and it still says "No one had it," even though we can see here that they took it out on the 10th. So we're going to add that code to our VBA here. DLookup still comes back with a zero. Case number two might be that the product has a start date, but no end date. So it's still out. We have to change our DLookup statement just slightly. In fact, I am going to just copy this and paste it here. And we'll tab it in, of course.

So now we're gonna say, look up the same thing, customerID from the rental table, where the productID equals ProductCombo, and the start date is less than or equal to the lookup date, and right here we're gonna change this, and we're gonna say, and If IsNull(the endDate), the endDate, just like that. Okay, what that's going to say is, look up and see if that particular customer had it on that date, they match the start date, but there is no end date'it's null. Okay, and here we'll say, if customerID equals 0, then now we can say "No one had it," and that's technically, I guess, case three. Case three, no one home. Let's make this a capital case right there. Capital "Case," "No one has it." Else, now we can say, the customer had it. "Has it, still out."

That is three different cases we had to check for, "if-then" loop and all that stuff, "structure statement." I'll test it, come back over here'well, always throw in a Debug Compile when you make changes like that. Don't forget your backups. All right, let's test that tricorder now. Click. "Customer 7 has it, still out." Look at that. Perfect.

Same? Let's go back and check, just to make sure we didn't break anything. Always double-check the stuff you already checked when you make changes because you don't want to break your code with something that you previously did. But let's just go back and check product 2, which was the handheld phaser. And that's still working. All right.

Did you like that? You want to learn more? In the extended cut for the members, we're going to take that code that we just wrote and rewrite it as a function. And with it as a function, we can then use it in different places in the database, including in a query. We can put it inside of a query and say, here's a list of all of my products and which customer had that product out on a specific date. We'll send the date and the product into the function. It'll return the customer, just like we just did. That'll be covered in the extended cut. Silver members and up get access to all of my extended cut videos. And gold members get access to my Code Vault with lots of cool stuff in it. And you can download these databases that I build in the TechHelp videos and lots more.

And of course, if you enjoy learning with me, come to my website and check out my developer lessons. I've got lessons for all levels. I've got from the very beginner all the way up to advanced stuff. And by the time you're done, you'll know just as much as me because I baked myself into the lessons. I took pieces of myself, and they're just'they're baked in there with a lot of love. So come and check it out.

But that is going to be your TechHelp video for today. I hope you learned something, folks. Live long and prosper. I'll see you next time.