Account Statements 3
By Richard Rost
19 months ago
Create Account Statements w Credit & Debit, Part 3
In this Microsoft Access tutorial, I will show you how to create account statements in a Check Register with Separate Credit & Debit. We'll tackle setting criteria in queries based on form inputs, designing forms with statement dates, and customizing report views, all while ensuring no VBA is used in the main segments. This is part 3.
Erratum
Thanks to Sami for catching that I forgot to add the +1 to the end of the formula at time index 2:30. It should be:
>=Forms!MainMenuF!StartDate AND <Forms!MainMenuF!EndDate+1
Members
In the extended cut, I will show you how to apply whatever filter the user sets on the check register form to the report. This way they can set filters for the dates, check number, cleared status, description, whatever - and those same records will show up on the report.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, create account statements, check register, separate credit and debit, print preview report, set query criteria, date range filter, forms data source, open form value access, button to open report, design view operations, report criteria setup, text concatenation Access, date value issues, format report header, dynamic date display, no VBA report button
Transcript
This is part three of my account statements series. You know what that means. If you haven't watched parts one and two, go get out of here. Go watch them. Come on back. All right. Where do we leave off? We have this credit report thingy here, print preview. All right. It's looking good, but it's showing all the values. What we want to do is limit it based on the start date and end date that the user puts in here. We set these up in the last class. Now we're going to make a button to open up that report. But before we open up the button, we have to tell the query where it gets its data from for it to get its criteria from this form. This is why I told you to go watch that "get the value from an open form" video in part 2. So now you're ready for part 3.
Now what we're going to do is we're going to say I want the criteria to include all records that have dates that are greater than this date and less than this date plus one. Now why plus one here? Well, sometimes you get people who put times in their date values. For example, they might have 'now' as the default value, in which case this might be 5-4 at 6 p.m. or whatever. Okay, so unless you're absolutely sure that these are only date values with no times, then you could use 'between' here, between this and this, and you'll be okay. Or you could use whatever. But the problem is people put times in their dates sometimes, so you have to think of that. So we want it to be greater than or equal to April 1st at midnight. Make sense? Okay, but it will include April 30th at 9 p.m. That's what we're looking for here. In fact, I got a whole separate video that explains that phenomenon when people put between start date and end date if your dates have times in them, they won't be right. So go watch this if you want to learn more about that. Query criteria giving wrong results. Okay, anyways, so we're going to come into our query that feeds the report. Right-click, Design View, and what we're going to do is we're going to add check date, or your date field, whatever it is, as a criteria. Turn this off so we don't show two of them. In the criteria field, Shift-F2, I'm going to zoom in. This has to be greater than or equal to Forms, Main Menu, F, Start, Date, and less than Forms, Main Menu, F, End Date. So include the start date at midnight, but don't include the end date at midnight. Okay?
All right, let's save it. You can run it from here, and look at that, it's working. Okay, now all we have to do is open up that report. And again, I promise no VBA in this one, so let's go to right-click, Design View. Let's go here, get a button, put it down here. Report operations preview reports. That's the report name. All right, let's go here, whatever, statement, account, statement, whatever you want to call it. Next. All right, statement button. Finish. Alright, back to the main menu. There we go, click the button, perfect, boom. There are your values. And notice how these are nicely sorted; you got your totals in there, everything looks good.
I also like to put something across the top, maybe across the page header or the report header, whatever you want to do. Let's put the word statement paste and I'd like you know it's for me and I like Times New Roman, I know it's old school. I love Times New Roman for reports; make it like 48 point, bold, all caps, it's professional, I don't know. I've always liked Times New Roman. Let's get rid of this silly color back here. Let's actually just use this thing up top. Shape fill. Okay, that looks good. And now I'd like to include something on here that indicates what the dates of the statement are. Because if we look at it now, it's just going to say statement. Maybe like right here we'll put between start date and end date.
Okay, Design View. I'll grab a text box, any one of them works. Copy, click up here, paste. I'm going to slide it over here to the right like so. And we'll put in here, let's say for transactions between forms main menu F start date and forms main menu F end date, just like that, oh and include an equal sign. Okay, alright? And if you don't know what these are, that's called string concatenation; that's covered in my beginner lesson, so go watch those. I'll put a link down below to a concatenation video for you as well, and we'll call this just a date notice, or whatever you want to call it, doesn't matter. Save it, close it. Let's make this a wee bit bigger. Let's go maybe 16 point, that looks good, and let's right-align it so it comes over to here. All right, I'm getting excited. Save it, close it. Let's open her up with the button. Oh, I got the border around it. That's another pet peeve of mine. I hate those borders.I usually don't encounter those borders because on my normal templates, I have them turned off, but this is an older template. So, outline, transparent. All right. Now I'm getting excited. There we go. Let's stretch it out. And, oh, looking good, looking good. You could put page numbers on the bottom if you want to. I got lessons on page numbering. I got lessons on everything. Just go to my website, use the search tool, and search for my videos on page numbers. And if you come across a topic that, you know, a Microsoft Access related topic, and I don't have a video for it, if you search YouTube, I want to know. So email me, let me know, and I'll make one. That's my goal. Anything anyone ever searches for Microsoft Access, I want my video number one. Okay, that's pretty much it. I mean, you can add some prettiness, some embellishments to it. You can format these dates differently if you want. Use the format command.
Some other things you might want, you might want some buttons in here. You know, go forward a month, backward a month. Now, let me show you how to do that. Let me show you how to make a button to go plus and minus a month. Now, we're gonna use a teeny tiny bit of VBA. I know I said no VBA, but this is a bonus, okay? If you want, go watch this, Intro to VBA. It teaches you everything you need to know in about 20 minutes. It's simple to use. I know a lot of people are scared of VBA. Don't be scared. It's simple. I'm going to show you how with two lines of code we can make little buttons that go plus and minus a month. Watch this. Come in here, Design View. I'm going to slide this down. Actually, let's make a new button. We'll put a new button right here. I'm going to cancel the wizard. And here, I'm going to put in here plus 1m, like that, for plus a month. You know what it means. Open it up. The name here is going to be plus 1m button. Let's make it plus 1m button. Doesn't matter. Whatever you want to call it. Right-click, build event. Now I got my build event automatically opening up my VBA editor. That's an option that I talk about in that intro to VBA lessons. If you get a window up that says what kind of builder do you want, pick the code builder. Okay, now here I am in the private sub plus one M button click. This is what happens when you click that button. Okay, what am I going to put in here? I'm going to say start date equals date add, it's the date add function. What's the interval? We want months, so it's M. All right, how many months? We want one. And what's the date you're adding a month to? The start date. It's that simple. That just adds one month to the start date. We'll do the same thing, copy and paste, for the end date. End date, just like that. Put it over here, end date. Save it, close that, close this, open it back up again, and watch this, plus a month. Uh-oh. What does this mean? You can't assign a value to this object. What? Huh? What are you talking about? Debug. Well, start date equals date add and start. Okay, I should be able to. Let's see. What's going on here? Let's hit this button that'll stop that. Hmm. Well, let's take a look. And I'm leaving this in the video because I get asked this a lot. People send me this in email a lot. Why can't I change this value? Usually, it's they're trying to change the VBA, but even so, if you come in here and try to change this, I'm hitting my keyboard, I can't change this. What's going on? Well, let's figure this out together.
What happened here? Why are these not editable? Are they locked somehow? Let's see. Let's go into here, design view. Let's open this up and does anybody see it? Do you see why I can't edit this? Take a moment, pause the video if you have to, and see if you can figure it out. Again, I get asked this one constantly. That's why I decided to leave this error in yesterday's video. Figure it out yet? Anybody? Post it down below in the comments if you figured it out before I show you the answer here. Alright? The problem is, are you ready for it? The problem is I put this equation in the control source. And if it's in the control source, that means this textbox is going to always be that value. You can't change it. It's not bound to a table field, so you can't put the data there. So it's going to always be equal to the first date of last month. What we should have done, what I should have done yesterday, and give yourself kudos by the way if you caught this yesterday and put a comment in yesterday's video, but this should not be in the control source. This should be in the default value. So let's cut it out of there, go over to data, and put it in the default value. Default value says you're going to start with that value, but the user can change it. Okay, click here, take this control source out, and put it in default value. And now if I save this, close it, open it, now I should be able to add a month. Look at that. Okay. Now, here's another problem that comes up. We can't just add a month to the end date. Why?Because you got situations like this. Here's September 30th. I add a month. Now, I go to October 30th, not the last date of the end date. So, we got to adjust our code. Okay. Let's go into our button again, right-click, Build Event. What we have to do is we have to say set the end date equal to the last day of this month.
So let's go back to that page with our equations on it. Alright, first day of month, let's go here. Last day of month, we'll take this, we'll copy this. That's the last day of the month that start date is in. Okay, see what I'm doing there? Alright, save it, close it, close it, open it. Now it will add one month to start date and give you the last day of that month. See, so you got to be careful with stuff like this. Alright, look at that. Isn't that beautiful? And if you want to make a button to go minus a date, that's easy to do too. Just do the same thing, make another button and in here just put minus one for the month in your other button. And if you want to do days, you can do days as well.
All the different options are in my date ad video. Go watch this guy. And on the date ad page, you'll see down here, here's all the codes, right, date ad, here's all the intervals. Be careful with years. It's YYYY, not just Y. Y by itself is day of the year, which is a number from 1 to 365. Okay.
If you want to learn more about how to build a more complex check register, I got a whole seminar on it, which we do pie charts and all kinds of more advanced reporting, and we can actually write checks. It's over five hours long. We do running balances on both forms and reports, printing checks we can actually have the words on them, one thousand five hundred dollars, whatever. That's pretty checks, pays categories, uh... you can have multiple accounts in your tables, you can have a checking account, savings account, whatever, and it's got all kinds of supporting, all kinds of cool things, so check it out.
Now one more thing, what if the user opens up the check register and applies some filtering in here, like let's say they only want to see checks. So, they come in here and they go, here we'll click on one of these and we'll go does not equal blank. So, now I have just actual checks with check numbers. What if I want to print a statement with just this stuff? What do I do? Well, this form has a filter on it. I'm going to show you how to take whatever filter is in this form, whether it's the date, whether you filter based on let's say you just want to see all the utility payments, right, gas, electric, so we filter those.
We'll take whatever filter is applied to this form and apply that to the printed report as well. And we'll cover that in the extended cut for the members. Silver members and up get access to all of my extended cut videos. Gold members can download the databases that I make in these TechHelp videos, which as you've seen in part one is very helpful sometimes.
Everybody, all my members get some free classes, so sign up today, hit that join button down below. And I think what might actually even be handier to do is to move those calculations here, to move those filters onto this form, get them off that main menu. So the user can do all their filtering and change the dates and whatever on this form and then when they're done hit the print statement button. Alright, and I'll show you how, when a filter's applied up here, you can change these buttons automatically.
Some cool stuff coming up in the extended cut. So, join today. And that's gonna do it. That's it for your TechHelp video. This is part three. This is probably the last part of the series for now, unless I add one in the future, I don't know. Sometimes that happens, people ask me a question, and I go, ooh, ooh, that's a really good one. Let me make part four. But as of right now, this is the last one. So look down below though. If I do make a part 4, I'll put a note down in the description below the video. But that'll be your TechHelp video for today. I hope you learned something. I hope you like this little 3-part series. Live long and prosper, my friends. I'll see you next time and members, I will see you in the extended cut.
TOPICS:
Setting report data limits by user-defined dates
Explaining date handling, including time within dates
Criteria configuration in queries for reports
Designing a user interface with a clickable button for report generation
Including dynamic dates in report headers via text boxes
Troubleshooting uneditable text box issues in forms
Implementing buttons to adjust date ranges in reports
Basic introduction and error handling in VBA programming
Quiz
Q1. What is the primary focus of the video tutorial discussed?
A. Configuring query criteria to filter records based on user-defined date ranges.
B. Learning how to write complex VBA code for database operations.
C. Designing a user interface with multiple buttons for a gaming application.
D. Installing and setting up Microsoft Access on a new computer.
Q2. Why does the instructor suggest adding '+1' to the criteria for checking end dates in queries?
A. To ensure the inclusion of times in date values.
B. To extend the report generation by one day.
C. To avoid duplication of date entries.
D. To fix errors in the database management system.
Q3. What does the instructor illustrate using a button in the video?
A. How to delete records from a database.
B. How to preview a report based on date filters.
C.How to create a new database table.
D. How to export data into an Excel spreadsheet.
Q4. Which problem is addressed towards the end of the video related to VBA coding?
A. Syntax errors in VBA functions.
B. The inability to edit text box values because they are bound to a control source.
C. Connection issues between Microsoft Access and external data sources.
D. Incomplete installation of essential software updates.
Q5. What is the purpose of the 'date add' function as used in the video?
A. To subtract dates from a specific field.
B. To reset the form fields to their default values.
C. To add a specified interval of time to a date value in queries.
D. To create a new query based on modified criteria.
Q6. When modifying the query to restrict records based on dates, what does the instructor ensure about the start and end dates?
A. Both dates are exactly the same.
B. The start date includes the time at midnight, and the end date does not.
C. The start date and end date are inclusive of the current system time.
D. The end date is set to include the next month.
Q7. What does the instructor recommend if users want to learn more about issues with date and time in queries?
A. Consult the database management system documentation.
B. Watch another specific video that discusses dealing with dates and times in queries.
C. Ignore the issue as it is not significant.
D. Rewrite the database schema to accommodate better date handling.
Answers: 1-A; 2-A; 3-B; 4-B; 5-C; 6-B; 7-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
|
