Today I'm going to show you how to take your check register database and find gaps in the sequence. So if you wrote out 101, 102, 104, it'll tell you 103 is missing or whatever you don't have there. So it'll give you a nice little list.

Today's question comes from Carlos in Pflugerville, Texas, one of my Platinum members. Carlos says, I'm using your check registry template. Is there any way to quickly figure out if there are any missing checks from the register? For example, if I've got 101, 102, 104, 105, I'd like the database to tell me 103 is missing.

Yes, Carlos, of course we can do this. There are a lot of different ways to do this. I had like probably five different ways in my brain that I could think of. You could use a record set loop. You could use a whole bunch of stuff. So I'm going to show you what I think is the easiest and most efficient way to do it. Are there better methods? Yeah sure, but I think this will work for most people.

Now the database that Carlos is talking about is the one we built in my check register TechHelp video. It's right there. The video itself is free, and you can build the database along with me. I am going to grab a copy of the template which is available for gold members. So again, another reason to join. You can just grab a copy of the database.

Now we have a lot of prerequisites for this class. We're going to be, of course, using some VBA. This is a developer level video, so if you've never used any VBA before, go watch this. It's about 20 minutes long. It will get you started.

We're going to use the if function, immediate if, it's basically if then in a can. It's one function that does an if then statement. We're going to need some type conversion functions, specifically CLNG, CLong, to convert a number to a long because the check number in this database is stored as a string value. I did that for a couple of reasons. We're going to use the isNumeric function. I just did this video a couple of days ago, in fact, so go watch this.

We're going to use a forNext loop to loop through our checks to see if they're there or not. We're going to figure out the lowest check number, the highest check number, and then loop through them and see if they're in the database. We're going to use the nz function, null to zero. And of course, you should know what null is and is null and is not null and all those null related null things.

You should know what variables are and how to declare them.

To figure out the lowest check number we're going to use D min
To figure out the highest check number you guessed it D max

And to see if that check exists in the middle we're going to use the grand pappy of them all the D lookup function. Now, these are all free videos. They're on my website. They're on my YouTube channel. Go watch all of those and then come on back.

All right, so this is the check register database that we built in that TechHelp video way, way many, many moons ago. I would say this guy is what? Let's see the date here. Four years old. Did it in 2020. Way back when, before I even had the TechHelp free template. And it's pretty simple. Here it is. All right, there's your check number, your date, your description, blah blah blah.

I do have a more advanced template in a seminar available where you can do multiple accounts and there's reporting and all kinds of stuff and I'll talk about that more at the end of the video, but for this simple one, we just simply want to say, okay, what's the lowest check number in here, then what's the highest check number in here, and then who's missing between those right now?

Since this is text, and I store these as text because you might want to put other stuff in here, you know, if you might like, you know, e-file deposit. Sometimes I put other things like that in my check number field, so we just want to check for the numeric values or make a query that's only gonna show us the numeric values from this field.

Here's what's both this critic where great where you design, create query design, bring in the check register table, the only table in the database. All right, I'm going to bring in for this particular query, we just need the ID and the check number. I don't care about all the rest of the fields here at all. And now here, we're going to determine if it's numeric or not. And if it is numeric, then we're going to put the long integer version of that check number here. Otherwise, we're going to leave it null. I want it empty.

Okay, so let's zoom in, shift F2. I'm gonna say, let's call this LNG check numbers, it'll be the long of the check number. If something, what's the something? Well, if the check number is a numeric value. So we use isNumeric of checkNum, that's a string value. If that's true, then this is going to be equal to CLNG of the check number. Otherwise, if it's false, put a null here. See that? It's if this, if it's true put that, if it's false put that. Okay? Hit okay. Let's save this. Let's call this my check number Q.

And now if I run it, you can see there we go. And notice, these are string values so they line up on the left side of the field, whereas numeric values always line up on the right side of the field, including dates. Now, for this one, for the purposes of this query, I don't care about all the non-numeric values. So we're going to come down here and put a criteria on it as well. Is not null. So I don't care about all this. I just want to see the numbered items. See that? Pretty straightforward.

All right, now that I've got this and take, you know, I like to take a little screenshot of this and I put it on my, you know, drop it in paint or whatever, put it, you know, just so you have it somewhere so you can see what the fields and stuff are. So you don't have to flip back over here and remember what your fields and stuff are.

Okay, save changes, yes. Let's go back to our form now and let's put a button right down here. Design view, grab a button, drop it there, cancel the wizard and we'll change the caption so it says find missing or whatever you want it to say.

All right, let's give the button a name over here, find missing button. Okay, right-click, Build Event. All right, so the first thing we're going to do is we're going to figure out what's the smallest check number in the table. All right, we'll need a variable to store this in, so dim min check num as a long.

Okay, min check num equals nz, that's our null to zero, d min what field? LNG check num from the check number Q. And if that returns a null, give me a 0. OK? So go to the query. Find the smallest check number, long check num. That's the calculated field we created. OK? If it doesn't exist, if this returns a null, if there are no check numbers, we're going to get a zero. And then what we'll do is we'll say here, if minCheckNum equals zero, then messageBox no checks found, exitSub.

Just like that. A little colon is what you can use to put two really short lines next to each other so you don't need the whole if, then, end if. So this should find the minimum check number. Let's just message box it real quick to make sure it's working. Message box, we'll just say min and min check num.

Okay. Save it. Debug compile real quick. Come back over here. Let's close it. Close it. Open it and hit the button. Bing! It's 101. Perfect. If I happen to have something in here that's like 44 and hit the button that's lower so we're good. Okay, so that's working so far. Now let's do the same thing, we'll find the max one, the max of those, same thing right max check num and I'm literally just going to copy and paste this. Copy, paste. Alright, we got max check, oh, forgot to define it as a long. Max check num is going to be d max of the long check number from the same query. If max check num equals 0, then no checks found.

Same thing. Let's just take a look at the max now. Max check num. Save it. Debug. Compile. Come back out. Yeah. Hit the button. Boom. And there's a 104. Okay, so we've got the max and the min checks in here and we've checked to make sure that there are checks. Now what we're gonna do, since we know the smallest one and we know the largest one, we're gonna loop through them and check for every check number in the middle.

Why? Well, sometimes you take a check, like I keep a check, an emergency check, in my laptop bag. So if I'm traveling and I need a check, I've got one there, but it's not registered in my thing. So it's missing. But that's why you might run into the situation. Or you might have a situation where you skip the checkbook. You got the little box full of checkbooks. And you accidentally went from the 500s to the 800s. You missed one in the middle. I've done that before too. So it's nice to know where your gaps are.

All right. So next up, we're going to make a for next loop in here to loop through all of those checks and see if they're in the table. We're going to do that in tomorrow's video. So tune in tomorrow, same bat time, same bat channel, you know the drill. If you remember, you can watch it right now because I'm gonna record it in about five minutes. But that's gonna be your TechHelp video for today.

I hope you learned something my friends. Live long and prosper. I'll see you tomorrow for part two.


TOPICS:
Check register database setup
Finding gaps in check sequence
Using VBA to identify missing checks
Setting up prerequisites for using VBA
Using if function and immediate if (IIF)
Converting strings to long integers (CLNG)
Using isNumeric function
Implementing forNext loop for checks
Finding the lowest check number using DMin
Finding the highest check number using DMax
Using DLookup to check existence of checks
Handling null values with nz function
Creating and saving queries in the database
Designing a query to filter numeric check numbers
Adding a button to the form to trigger the sequence check
Writing VBA code to find minimum check number
Writing VBA code to find maximum check number
Looping through check numbers to find gaps

COMMERCIAL:
In today's video from Access Learning Zone, I'll show you how to find gaps in your check register database. We'll start with Carlos' question from Texas, using a check register template to spot missing checks in the sequence. First, we set up our database and talk about basic VBA essentials. Then, we create a query to identify numeric check values and use VBA to find the lowest and highest check numbers. Next, we loop through the range to find any missing checks. This tutorial covers practical steps to ensure your financial records are complete. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.