Today is part 2 of my account statements series. If you haven't watched part 1 yet, go watch that first. You'll find a link down below. Then come on back. You're holding up class. Go on. Get out of here. Go watch it.

Alright, so yesterday we got a lot done. We built the report up to this point here. I think after looking at it, I'm also going to get rid of that line that I put in there. I don't like the way it looks. But first off, we also have to fix our totals down here. Now this right now is a total for everything. I want a total for each of these sections.

So let's go back into here, design view, and yeah, I'm going to go to that one. I don't like it. I forgot that these guys have borders around them, so I like the way it looks like that.

Alright, so we're going to take this stuff out of the report footer. Actually, I'm just going to copy it. So let's copy all of this. And, oh, you know what? We don't need separate debit and credit in here, do we? No, we do not, let's see. Yeah, we don't need debit and credit, just amount. Just amount, so design again. I don't know why I didn't do this last time, but there we go, get rid of you, get rid of you, get rid of you. We'll just expand the description here out to there. There we go.

Okay, now I do want this amount up in here in the title first, so I get a total for each section. So I'm going to copy that with its line, copy, click in here, paste. We're going to slide you over there like that. And is it just a bit too far? Let's bring it. Yeah, we'll bring you over here. Sometimes you don't see these until you look at them closely. You can leave a little bit of extra space in here if you want to. Maybe bold this one, make it darker so format shape outline thickness little bit thicker cc right save it let's take a look at what we got, okay, looking good twelve hundred there fifteen seventy there seems about right, now this isn't correct so what we have to do is we have to take the sum of the credits and subtract the sum of the debits. I don't want to add all these things up. So let's modify this guy, go to data, it's not going to be sum of amount, it's going to be sum of credit, let me zoom in so you can see it, shift F2, sum of the credit minus the sum of the debit.

Okay, and yes, you don't need to put these little brackets in here because you don't have any spaces in your field names, like good little programmers. In fact, I usually remove these but Access puts them back in there for you anyway.

Makes it easier for beginners to understand. Okay, alright, save it, close it. I'm going to make this a little bit smaller. Okay, ready? Let's take a peek. Right click, print preview, and looks good. That looks correct. Looks like we had more money going out this month than coming in. Maybe some titles in here. Sum of debits, sum of credits, and then total. That'd be nice. So I'm going to put this in a text box. I'm going to grab any one of these. Grab description, that's fine. Copy, paste, and we'll put it right here, and maybe about that big.

Open her up. Now we don't want this guy bound to description anymore. So we're going to come in here, and the control source is going to be equals, let me zoom in again, shift F2, equals sum of and title. Title is a field that either says debits or credits. That's perfect. Looks good. Then we'll put in here, let's put in here title, whatever you want to call it. And then down below here we'll just put total. You could use a label for that. You don't have to make a text box for that one. That's just a total. Slide you down here. So change that to total. If you want to right-align these, that's fine. Whatever you want to do. There you go. Maybe make this guy bold. And see, these aren't exactly the right size. It's one of my pet peeves. They don't automatically come in on the grid. Let's see. Right click, size to grid. There we go.

Let me do these ones. While we're at it. Right click, size to grid. I know it's off your screen. That's a beginner thing. You should know that. Save it, close it, right click, print preview. OK. Whoopsie. Looking pretty good.

Yeah, the stupid borders, I know. Alright, right click, design view, view, format, outline, transparent, you and you outline transparent. Okay, what else do we need? Well, next we need a way to add some date criteria to this. Okay, because right now I'm just seeing all the records of my form. In fact, let me update the dates in here. It's currently May eighth of twenty twenty-four, so let me back this up to let's go 4-20 on that one. I'm just going to make these a little more current. Let's go 4-23.

Actually, let's back this up to March, so 3-25, because we want it to be before our valid date range. Let's make you 4-25. I'll go 4-26. And then we'll just change this one so they're a little bit out of date, so 5-2. If we do a monthly, I want to see them. And then a 5-4.

Okay. So if we want a last month statement, we should just see these ones, right, from April. Okay. Now the easiest way to do this with a report like this is to put your criteria on a form. Okay? You could put it on this form and then hit a button to open it, but I think that sometimes gets confusing because you'll have different criteria down here, you'll still see all of them up here, you could change the filters, I mean it just gets crazy. So this is where a menu comes in, very, very handy. You could put it on a main menu, you could make a report menu, somewhere else where you have the criteria, and then when you click the button, the form provides the criteria. If you don't know how to do this, go watch this video; it shows how to get a value from an open form.

Alright, here I have a little form. It's got just a state in it. You click the button and it provides the criteria to this query. Okay, go watch this video. Find the link down below. Now I just happen to have a nice little form main menu already set up in my TechHelp free template. You guys I'm sure have seen this one. Come on, open up. Come on. There it is. This guy. I'm just going to grab the main menu out of here, click and drag and drop it in this one. Okay, that's it. Okay, you're done. Bye.

Here's the main menu. Alright, it's got buttons open to custom form, which these things don't exist. So let's just go in here, design view. I'm going to close you for now. Okay, we don't need these buttons because those things don't exist. I'm not going to use this status box. We'll make that nice and small. But what I do need are some buttons to open up the check register form and then some criteria for my statement. Okay? So let's, you know what, let's delete this. I promised I was going to do this without any programming because normally you just throw a line of VBA code in there. But let's create a button, form design, command button, drop you here. Alright, we're going to go form operations, open a form, we're going to open the check register form, we're going to show all the records, and then for text we'll put in here, check register form. OK, check reg form button, and there you go. There's my open the check register form, just to make sure that it works. There you go.

Now, we need criteria for our statement. So we already have one little box here with a date in it. This is just a text box. Alright, and we'll put over here, let's say this will be the start date. Now I'm going to open you up. I don't want today's date in here. You can start with today's date if you want. Let's name this, by the way, let's name this start date. Now instead of it being today's date, let's say by default you are always printing out last month? Wouldn't that be cool? Well, how do you do that? Well, I just happen to have a video. It's called First Day of the Month. How do you find the first day of the month, the last day of the month, the first day of next month, the last day of next month, the first day of the previous month, and all those different things are on this page. Isn't that cool? It makes sense to watch all my videos, right Sammy? Sammy's one of my moderators and he's on a quest to watch all of my TechHelp videos. I think he's up to like what 2022 now Sammy? Anyways, if you go to the page for this class, look at this. Oh here we go right down here, Usage. All right, first day of the previous month is right there. So we're gonna copy this. The only change you have to make to it is if you want the actual previous month, you got to replace D with the date function. That's it. All right, so we're going to copy that. We're going to come up here and in this control source, I'm going to shift F2, zoom in. All right, I'm going to say this is equal to that function, but all we've got to do is replace D, which is a date, with the date function. Alright, and what that will do, is it will put today's date in there. So now, our start date, if I close this and reopen it, will default to the first day of last month. Like I said, it's currently May 8th, so that's the first day of last month. Isn't that cool?

Alright, let's do the end date. Copy paste. Oh, get over there. Okay, end date. And we'll call you end date. And what's the function? Let's take a look at the page. It's going to be the last day of the previous month which is right there. And yes, I have a whole separate video on date serial if you want to learn more about how this thing works. I'll put a link down below. So just copy that formula. We're going to come into here. I'm going to shift F2 to zoom in. We're going to replace that with this. Replace these with today's date. Like so. Hit OK. We're going to save it, close it, open it, and there we go. There's our criteria. Now we're cooking with gas. We're getting there, folks. We're getting there. Now we just have to feed these dates into our report when we open it up, and we'll cover that in tomorrow's video. So tune in tomorrow for part three. Same bat time, same bat channel. Or of course, if you remember, you can watch it right now because I'm about to record it in about two minutes.

In the meantime, if you want to learn more about how that date serial function works, go watch this. And if you do a lot of work with dates and times, I have several lessons in my expert series where I cover all the different date time functions and I put them all together in one seminar called the Date Time Seminar. All right, it covers everything you ever wanted to know about working with dates in Access: workdays and holidays and all kinds of time date calculations, you name it. That's covered in this Date Time Summary. Lots and lots and lots about dates and times and reminders and pop-ups and units, how to figure out holidays, and it's all in here. It's like ragu, it's in there. Or is that prego? One of those spaghetti sauces, I don't remember. All right, but that's going to do it for part two. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part 3.

TOPICS:
Report design modifications in Microsoft Access
Implementing section-specific totals in reports
Adjusting report layouts for clarity and aesthetics
Simplification of report elements
Transposing items among report sections
Calculating net amounts by subtracting sums
Dynamically displaying titles based on data conditions
Adding functional text and labels in reports
Setting transparent outlines for report items
Incorporating date filters for statement reports
Techniques for linking forms to reports as criteria providers
Defaulting date selections to specific ranges using expressions
Navigation and menu setup in Access for report interaction
Creating command buttons for form operations