Account Statements 3
By Richard Rost
2 years 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
Intro
In this video, we continue the Microsoft Access account statements series by focusing on how to limit report data based on user-selected start and end dates, set up query criteria to handle date and time values correctly, and create a button that generates these reports from your form. I will also show you how to include the selected dates dynamically in your report header using text boxes, troubleshoot issues with uneditable form controls, and introduce a simple VBA solution for adding buttons to adjust date ranges by months. This is part 3.
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.
Summary
Today's TechHelp tutorial from Access Learning Zone continues our series on account statements with part three. If you have not already gone through parts one and two, I recommend doing so first, as we will build directly on those lessons here.
Where we left off, we had a report that displayed all credit records, but now we need to limit the records shown based on a user-selected start and end date. These date fields were set up previously. To ensure our report only shows records within the specified date range, we first have to adjust our query so it pulls criteria from the form fields. This method of referencing form values in criteria was explained in a prior lesson on using values from open forms.
For accurate filtering, we want the report to show records with dates greater than or equal to the start date, and less than the end date plus one day. The reason for adding one day to the end date comes from the potential for time values being included in date fields. If someone uses a date field containing a time (for instance, 'now' instead of just the date), using 'between' incorrectly could exclude some records, especially those entered during the day of the end date but after midnight. So, using greater than or equal to the start date and less than the day after the end date captures all records from the start of the first day through to the end of the last day, no matter what time they occur.
If you want to know more about why including times in dates can affect your query results, I have a dedicated video on that topic: it explains why using 'between' can cause you to miss results when dates carry time values.
With that explained, we go to the query used by the report and enter the proper criteria for our date field. We set the criteria to be greater than or equal to the start date from the form, and less than the end date from the form. This means our reports will include records from the first minute of the start date through the last minute of the end date.
Once this is set, we can test the query and confirm it works as intended. After that, we return to the form and add a button that will open the report for the user. No VBA is needed for this step: simply insert a button using the interface, select the report you want to open, give it a name, and place it on your form. Now, users can click this button to see their statements.
To make the report more informative and professional, I like to add a clear title at the top of the report. For instance, I place a textbox in the report header area and give it a bold, distinctive font like Times New Roman at a large size. This maintains a professional look.
It is also helpful to indicate the date range of transactions shown in the statement. To do this, you can add a textbox to the report displaying the start and end dates, pulled from the form—using string concatenation so the report header might read, for example, "For transactions between [start date] and [end date]." If you are not familiar with string concatenation, I encourage you to watch my beginner tutorials where that topic is explained in detail.
Now, one issue that often confuses people: if you notice you cannot type into your form fields, or VBA code cannot change a value, check to see if you accidentally set an equation in the 'control source' of the textbox. If you want a textbox to start with a certain value but remain editable for the user or code, use the 'default value' property rather than the 'control source.' This is a common pitfall, and moving the formula to 'default value' allows the field to be updated as needed.
At this point, you might want to add buttons that let the user shift the report date range by a month forward or backward. To do this, you can create small buttons labeled with something like "+1m" or "-1m." These require just a tiny bit of VBA, and I assure you, it is nothing to be intimidated by. In the button's click event, you adjust the start and end dates automatically using VBA functions. If you want both dates to match a full calendar month, you may need to use additional code to ensure the end date is set to the last day of the correct month, even for months of varying length.
If you run into issues with this, such as an error when trying to change the date in code, remember the earlier reminder about the control source vs. default value. Setting the wrong property can make the field uneditable.
You can also add other handy features, like page numbers (covered in my other lessons), formatting date text with the Format function, or offering the ability to skip forward or backward by days, weeks, or years, all outlined in my date manipulation tutorials.
If you are interested in taking this further, especially building more feature-rich checkbook registers with advanced reporting, visualizations, and support for multiple types of accounts and categories, I have an in-depth seminar covering those more complex scenarios.
Lastly, if your users might apply filters directly within the check register—for example, to only show checks or certain payees—and they want these filters reflected in the report, it is possible to capture the form's current filter and apply that to the report. I will walk through this topic in detail in today's Extended Cut lesson for members.
For those interested, Silver members and higher get access to all extended cut videos, while Gold members can download all databases used in these tutorials. All members get access to some free classes, and your support helps me keep providing these lessons to the community.
That wraps up our walkthrough of account statement generation and related form interactions. If there is ever an Access topic you cannot find covered either on my site or on YouTube, let me know and I will be happy to consider it for a future lesson. If I add another part to this series, I will update the information on my site and in the video description.
You can find a complete video tutorial with step-by-step instructions on everything we covered here on my website at the link below. Live long and prosper, my friends.
Topic List
Setting report criteria based on start and end dates
Handling times included in date fields
Configuring query criteria to reference form values
Designing a button to open reports from a form
Adding a dynamic date range notice to report headers
Concatenating form values in report text boxes
Fixing uneditable text box issues caused by control source settings
Moving formulas from control source to default value
Creating buttons to increment date fields by one month
Using the DateAdd function in VBA for date manipulation
Adjusting end date to last day of the month in VBA
Creating buttons to decrement date fields by one month
|
