Number Pairs 2
By Richard Rost
2 years ago
Break Years into 2-Digit Number Pairs in Access Part 2
In this Microsoft Access tutorial, I will show you how to break years into two-digit number pairs for speech synthesis. We'll handle nearly all years, adding special exceptions and creating a public function to simplify repetitive code. This is part 2.
Members
There is no extended cut, but here is the database download:
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, Two-digit number pairs, speech synthesis, Microsoft Access, VBA function, year formatting, format year function, century exception handling, three-digit years, four-digit years, year parsing, digit pairs in text, split years in VBA, number pairs in Access, Richard Rost tutorial
Subscribe to Number Pairs 2
Get notifications when this page is updated
Intro In this video, we'll continue working with Microsoft Access VBA to break up years into number pairs, expanding on the logic from part one. I'll show you how to create and use a new function to handle exceptions, including full century years, optimize your code by removing redundant variables, and format numbers from 1 through 9999. You'll learn how to use if statements, the modulus operator, and handle both three-digit and four-digit years for accurate formatting. This is part 2.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part two of my breaking up years into Number Paris. So it says 1972 or 2156 or whatever you want it to say. If you haven't watched part one yet, go watch that and then come on back.
All right, so we've got this working for most years, like 1915 or, you know, whatever. But if you give it a whole century year, that doesn't sound about right. So let's add some exceptions to our rules over here. Now, one thing I'm going to do is we're going to create a bunch of exceptions. So I'm going to take this, replace the old part. I'm going to make this its own separate function.
Okay, so we're going to slide up here, and we're going to say public function add o s as a string, as a string. All right, so we're going to basically send in a two-digit string year that we've already built down below, and we're just going to do that replacement. So if left s, comma, one equals zero, then add o equals o and right s, comma, one. All right, otherwise, add o just equals s. So if I send it 18, it's just going to return 18.
Okay, so now because I can use this in multiple places now, I don't want to duplicate my code. So we can get rid of this whole block. Okay, and in fact, we don't even need to split this anymore. I like to get rid of variables whenever I can. So let's go backwards. Let's get rid of that. Let's get rid of left two and right two. We're just going to replace this with left two going back here and right two is going back here. But now I can say add o to that.
See how that function makes things a lot simpler? And now I can go back to this. All right, split into pairs. Okay, let's test it. Thirteen oh zero. Oh yeah, we still got that, we still got that problem, but we'll get to that in a second. Thirteen oh five. Okay, so that's working great. All right. And another thing I was thinking is there's no reason why we shouldn't be able to easily handle numbers going all the way down to one.
So we're just going to say here if this is, if y is greater than 9999 or let's do or if it's less than, let's do if it's less than 99. All right, because anything less than 99, just going to say it. 84, 17, 62, sure. All right, replace with that. And anything bigger than 9999, we don't want to deal with either. Okay, so now that we're down here, now we know we've got a year from 100 to 9999.
Okay, so here let's deal with three-digit years. And this works with numbers too, if you're dealing with dollar signs, you know, currency values. Okay, so here we can say if y is less than or equal to 999 then, and if here we're going to check first to see if it's an even 100. Okay, how do we check to see if it's divisible by something? We use the mod function or the mod statement or whatever it is. It's the modulus operator. It's an operator. I always forget that. There's functions, there's subroutines, there's operators, there's all kinds of stuff.
So we use the mod operator to determine if something is divisible by something else. So if y is less than or equal to 999, in fact, you know what I'm going to do, I'm going to move, let's leave it there, that's fine. Let's say if y mod 100 equals zero, then we have an evenly divisible by hundreds, all right, so even hundreds. This will be a year like 1900, 1800. Well, actually here we're less than a thousand. So this will only be the years like 800, 700. We'll deal with the other ones in just a minute.
Now, if memory serves, access will say these just the way you expect, 700, 800, 900. So here we can just say that format year equals s, our value. Otherwise, if it's not evenly divisible by 100, then we got a regular number like 124. So we want it to say 124, just like we were doing down here. So we're going to copy this, we're going to split it up, and we're going to put it here. And we're just going to say, give me the left one character and then the right two. So it'll be like 764. Save that.
All right, now in here, we need an else, and this will be four-digit years now. All right, I'm going to move this so it's consistent. Let's move this over here. All right, so here's our three-digit years. Down here is going to be our four-digit years. Because right now, we're less than 999 up here. So that means we're over 999, but still less than 10,000. So the first thing we have to check for is even thousands. So if y mod 1000 equals zero, then we have an even thousand. And this will be a year like 1900. Right? Oh, no, I'm sorry. It'll be a year like 1000 or 4000.
So we're just going to say that. The 1900 is coming up next. All right, so this again is going to be format year just equals s, say 2000. Okay, else if, now at this point, what if we're divisible by 100? Right, so if y mod 100 equals zero, then we have even hundreds over 1000, over 1000, yeah. Okay, and this is where we're going to say format year, format year, I can't type today, equals, we want the left two digits and then the word 100. 1600, 2400. So this will be left s, two and space 100, the actual word 100.
Okay, otherwise, well, what do you have left at this point? This stuff right there. Paste it in there. Right, yeah, regular, whatever. In fact, I'm going to move this up to here. Don't paste twice.
Okay, and that ought to do it. Let's see, yeah, I've been a little inconsistent throughout here. This should be format year equals this. I've been using s when I didn't need to be using s. Yeah, we got s is that here, so format year equals s, okay, format year equals s, format year equals that, format year equals s, so I'm going to get rid of it down here and I'll put it there because I've been using s to pick it apart and then putting the value in the actual format here.
Okay, I think we're good. I think this is what we need. Let's save it, debug, compile. Let's come back out and let's try it. 1305. Okay, that sounds good. Let's go 500. 500. All right, let's go 506. 506. Beautiful. Let's do a thousand. 1000. Okay, let's do 1300. 1300. Beautiful. Let's do 1305. 1305. Wonderful. Let's go 1995. 1995. Sweet. About 1905. 1905. All right. 2255. That's too big. 2255. Well, let's test that. 22554.
You could very easily continue extrapolating this out now if you wanted to put another one down here. Right, just you'll have to do this again and another else if and go to five digits. So you can make these as big a number as you want if you want to keep doing number pairs. But at that point, it doesn't make sense to do number pairs. That's not really how we speak those years. Right, well, I guess our great, great, great, great, great, great, great, great, great ancestors will have their descendants will have to worry about that. Right, but there you go. There's your format here and your add-o function.
I'll put these in the code vault for you gold members, and you can also grab them right out of here. Everybody else gets to type in or join up as a gold member today. Right, all right, so that's going to do it. Easy peasy. That's your tech help video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
Which is completely free. Click the bell icon and select all to receive notifications when new videos are posted. Want to learn more? Click the show more link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there. See this part of the description here. The name, the videos up here. There's a little show more down there, right down the bottom. It's kind of hard to find. But once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
And YouTube no longer sends out email notifications when new videos are posted that they used to do. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list. And you can pick how frequently to get emails from me, either as they happen daily, weekly, or monthly.
Now, if you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the join button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my code vault, lots of VBA source code in there, template downloads, and lots more. I'll talk more about these perks at the end of the video.
Even if you don't want to commit to becoming a paid member, and you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I got some puppies to feed. But don't worry, no matter what, these free tech help videos are going to keep coming. As long as you keep watching them, I'll keep making more. And they'll always be free.
Now, if you really want to learn access, and you haven't tried my free access level one course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over four hours long. You can find it on my website or on my YouTube channel. I'll put a link down below you can click on. And did I mention it's completely free? The whole thing, free, four hours, go watch it.
And okay, okay, a lot of you have told me that you don't have time to sit through a four hour course. So I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster in about 30 minutes. And no, I didn't just put the video on fast forward. But I'll put a link to this down below as well.
Now, if you like level one, level two is just a dollar. That's it, one dollar. And that's another whole like 90 minute course. Level two is also free for paid members of any level, including supporters. So if you're a member, go watch level two, it's free.
Okay, want to get your question answered in a video just like this one? Visit my tech help page and send me your question there. Members get priority, of course. While I do try to read and respond to all of the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month, and sometimes I get thousands of them. So send me your question here on the tech help page, and you'll have a better chance of getting it answered.
And while you're on my website, be sure to stop by my Access forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and of course on YouTube. Yeah, I'm on Facebook too, but I don't like Facebook. Don't get me started.
Now, let's talk more about those member perks if you do decide to join as a paid member. There are different levels: silver, gold, platinum, and diamond. Silver members and up get access to all of my extended cut tech help videos, one free beginner class every month, and some other perks. Gold members get all the previous perks, plus access to download the sample databases that I build in my tech help videos, plus access to my code vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any tech help questions.
Now, answers are never guaranteed, but you do go higher in the list for me to read them. And if I like your question, you got a good chance of it being answered. You'll also get one free expert level class each month after you've finished the beginner series. Platinum members get all the previous perks, plus even higher priority for tech help questions. You get access to all of my full beginner level courses for every subject, and I cover lots of different subjects like Word, Excel, VBA, ASP, lots of different stuff, not just access. These are the full length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month, so lots of training.
And finally, you can also become a diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon. Bye.
TOPICS:
Creating a new VBA function for exceptions
Using the public function in VBA
Sending in a two-digit string year for processing
Creating exceptions for whole century years
Optimizing code by removing redundant variables
Handling numbers down to one
Checking conditions with VBA if statements
Dealing with three-digit years
Using the modulus operator to check divisibility
Handling four-digit years
Formatting years based on digit count and conditions
Ensuring consistency in variable use and assignments
Debugging, compiling, and testing VBA code
Extending the logic to handle larger numbers
Extrapolating code for future higher digit years
COMMERCIAL:
In today's video, I'm continuing our journey to break up years into number pairs. We'll tackle exceptions for full century years, create a separate function for easier handling, and refine our rules for both three-digit and four-digit years. I'll walk you through examples like 1305, 500, 1995, and beyond, ensuring you can handle any year from 100 to 9999 with ease. If you missed part one, make sure to catch up first. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the main topic of this video tutorial?
A. Creating databases in Microsoft Access
B. Breaking up years into number pairs
C. How to handle currency values in Access
D. Building queries in Microsoft Access
Q2. In this tutorial, which operator is used to determine if a year is divisible by another number?
A. Divides operator
B. Modulus operator
C. Quotient operator
D. Remainder operator
Q3. What is the purpose of the 'add-o' function introduced in the video?
A. To concatenate a string to another string
B. To add ordinal suffixes to dates
C. To format two-digit year strings
D. To replace variables in the code
Q4. How does the tutorial suggest handling years that are greater than 9999?
A. Format them the same as any other year
B. The tutorial recommends not dealing with them
C. Use a separate function for these years
D. Convert them into a different data type
Q5. How are three-digit years (like 764) formatted according to the tutorial instructions?
A. Using the 'left' function only
B. Concatenating the first digit with the last two digits
C. Using a special exception handling
D. Splitting the digits into pairs
Q6. What modification does the tutorial suggest to handle numbers less than 99?
A. Writing a new subroutine
B. Adding an additional condition in the 'if' statement
C. Using a different data type to store the numbers
D. Ignoring these numbers completely
Q7. How does the video propose formatting years that are divisible by 1000?
A. Leaving them as they are
B. Adding the word 'thousand'
C. Formatting them as 4-digit numbers
D. Using the 'format year' function similar to other years
Q8. How does the tutorial suggest checking if a value is even?
A. Using the 'even' function in Excel
B. Using the 'mod' operator to check for a remainder of zero
C. Dividing by two and checking the result
D. Using a custom VBA function
Q9. What does the tutorial say about handling years like 1000 or 4000?
A. Format them by adding the word 'hundred'
B. Treat them as exceptions and process them separately
C. Format them using the 'format year' function directly
D. Combine them with other year pairs
Q10. Which piece of code is repeatedly corrected or updated during the tutorial?
A. The input handling function
B. The 'format year' assignments
C. The date parsing function
D. The exception handling block
Answers: 1-B; 2-B; 3-C; 4-B; 5-D; 6-B; 7-B; 8-B; 9-C; 10-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 exploration of breaking up years into number pairs using Microsoft Access VBA. This is part two of the series, so if you have not already watched part one, I recommend you start there and then return to this lesson.
Previously, we built a function that worked well for most years, such as 1915 or 2156. However, things did not sound correct when the input was a whole century year like 1900 or 2000. To address this, it was necessary to introduce exceptions in the logic. I started by creating a new function specifically for these exceptions. The goal was to improve the modularity of the code, so I took out the relevant parts and made a separate public function that handles the processing of a two-digit year string. This allows us to easily reuse the logic wherever it is needed, removing duplicate code and unnecessary variables in the process.
By structuring the code in this way, the function could efficiently handle combinations like "18" being returned as "18," but also dynamically process other two-digit sequences. This made the core part of the program leaner and clearer.
Testing the new function showed that it handled typical years well, and refining the checks made it possible to also process single- and two-digit numbers accurately. For instance, if a value is less than 99, it simply returns the number as it is, covering cases like 84, 17, or 62.
Once a robust foundation was in place for simpler numbers, I adjusted the logic to focus on three-digit and four-digit years, as these represent the majority of input cases an Access application might need to handle. Checking conditions for even divisions, such as determining if a year is a "clean hundred," required using the modulus operator. The mod operator is essential for checking if values like 700 or 800 are divisible by 100 without a remainder.
For three-digit years divisible by 100, the function outputs them exactly as expected. For other three-digit values like 124 or 764, it breaks the number into logical parts before constructing the output. The same thinking was applied to four-digit years. The program first checks for even thousands with the mod operator (for instance, 1000 or 4000) and outputs those values as is. Then, it checks for "clean hundreds" greater than 1000, such as 1600 or 2400, and formats them by taking the left two digits and appending the word "hundred."
Any remaining cases are handled by splitting the number as needed. This all ensures consistent variable naming and assignment throughout the code, improving reliability and readability.
After the changes, I performed thorough testing: entering years like 1305, 500, 506, 1000, 1300, 1995, and 1905 resulted in the correct spoken-like formatting. If someone wants to continue extending this logic for higher digit years, such as five-digit numbers, it is a matter of repeating the approach with more conditions as necessary. However, that kind of extrapolation is mostly for hypothetical future needs.
The two core pieces to take away are the year formatting function and the helper function that refines those outputs. Both are valuable additions to your own code repositories.
If you are a Gold Member of my site, you can find this code in the Code Vault. All others are welcome to type it in themselves or consider upgrading to gain access to a wide range of sample databases and VBA functions.
As always, if you have questions you would like answered in a video, visit my TechHelp page to submit them. Members are given priority, though I try to get through as many questions as possible from all users. You will find lively discussions in the Access Forum on my site, and many resources are available to help with Access, from free beginner courses to more advanced material.
For those wishing to support these free tutorials, there are multiple membership options, with perks such as access to extended cut videos, the code vault, sample databases, and more. Whether you are just starting out or looking to take your Access skills to the next level, there is a level for you.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a VBA function for handling year exceptions
Processing two-digit string years in VBA
Handling exceptions for full century years
Refactoring code to avoid duplication
Removing redundant variables in VBA code
Handling numbers less than 99
Excluding numbers greater than 9999
Processing three-digit years in VBA
Detecting even hundred years using Mod
Processing four-digit years in VBA
Detecting even thousand years using Mod
Formatting years ending in hundreds over 1000
Extracting digit pairs for year formatting
Ensuring consistent variable assignment
Extending logic for larger numbers and higher digit years |
