Number Pairs
By Richard Rost
16 months ago
Break Years into 2-Digit Number Pairs in MS Access In this Microsoft Access tutorial, I will show you how to break up a year into two-digit number pairs so that speech synthesis reads it correctly. We will cover string functions like left, right, and mid, and create a custom VBA function to format years such as 1865 into "18 65" for better speech output. Rachel from Westminster, Colorado (a Platinum Member) asks: I have poor vision. Thanks to your Text to Speech video, I have my Access database say a lot of things for me. One thing that irritates me, however, is when it comes to a year. For something like 1865, it will say "one-thousand, eight-hundred, sixty-five" instead of how it's normally spoken "eightteen, sixty-five." Any tips on how to make this sound better? PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, breaking years into number pairs, Microsoft Access speech synthesis, Access VBA text-to-speech, format years in speech, speak year in two-digit pairs, text-to-speech for years, synthesizing years in Access, VBA functions for years, Microsoft Access custom functions, text-to-speech formatting Access
Subscribe to Number Pairs
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today we're going to talk about breaking up a year into two-digit number pairs so it sounds right if you have speech synthesis going on. The computer doesn't say 1,972; you want it to say 1972. That's what we're going to do in today's video.
This question comes from Rachel in Westminster, Colorado, one of my Platinum members. Rachel says, I have poor vision, I feel you. Thanks to your text-to-speech video, I have my Access database say a lot of things for me. So do I. One thing that irritates me, however, is when it comes to a year. For something like 1865, it will say 1,865 instead of how it's normally spoken, 1865. Any tips on how to make this sound better?
Yes, it's actually interesting, Rachel, because if you type in a year from 1900 up until I think 2100, it'll say it like that. It'll say 1972 or 2024. But if you type in something like 1865, it'll read it out like 1,865. So there's definitely a cutoff in there somewhere. I'm not exactly sure where, but today we're going to make it so that you can send any four-digit number and it'll correct it like that.
All right, before we get into it today, make sure you go watch my text-to-speech video. It's very easy to make Access speak. VBA's got its own speech library, and that's not the best, but it works. So go watch this. And of course, today we're going to be using some VBA to do what we need to do. So if you've never programmed in VBA before, go watch this video. It'll teach you everything you need to know to get started in about 20 minutes. Make sure you understand how variables work. We're going to be using some of the string functions like left, right, and mid, all those guys. Make sure you know how those work. Be sure you understand string concatenation, that's putting two strings together. And today we're going to create a couple of custom functions, so go watch this video too. These are all free videos. They're on my website. They're on my YouTube channel. Go watch those and come on back. There might be some more. I'm not sure. But if there are, I'll point them out.
All right, let's get to it.
Okay, here I am in my TechHelp free template. This is a free database. You can download it on my website if you want to. The first thing I'm going to do is plug in the speech synthesis code, so let's go to the code vault. I'm going to go to my website and search for speak or speech or whatever. You'll see it right there. There are the text-to-speech videos there, and down here in the code vault, there's the text-to-speech. This one's free. Anybody can grab this one. I'll put a link to it down below, okay? And this is all you need right there. It's what? One, two, three, four lines of code. All right, so copy that to your clipboard. Only, this is the kind of cool stuff that you find in the code vault, though. So that's what? It's the benefit to be a code member.
All right, back in the database, I'm going to find my global module. There it is. Open her up and resize this. Okay, I'm just going to come right down here to the bottom and hit paste. And there's my speak subroutine. So all you got to do is tell Access to speak and then it'll say some stuff. So let's just test it. Let's come out here, design view. I am going to put whatever I want it to speak in this box here, all right? So speak this. We'll call it. And I'll make the name of this box, instead of current date, we'll make it speak this. We'll get rid of the control source. We'll get rid of the format. And then we'll change this button to speak. You can leave the name hello world button, that's fine. And what we're going to do in here is right-click, build event. And instead of status hello world, we're going to say speak, speak this. And it's going to send, speak this, whatever's in that box to speak and you should hear it. All right, let's test it. Save it, close it, put in here 1913 and speak.
1913. See, it says that one right. If you make this 1813, watch what you get.
1813. Okay, see, yeah. About 2024, let's see.
2024. Okay. How about 2124.
2124. All right, so we're going to make it so that any year, any four-digit year gets broken up like that into two pairs. So this will be 2024. And of course, then we got an issue with this. How do you handle that zero?
2007. Yeah, see, some people want to hear 2007. So or 1907. What's 19 do?
1907. Yeah, so that one's right. What about 1807. I'm just trying to get a feel for all these different numbers that it does.
1807. Yeah, see, and it shouldn't say and according to the proper rules. And is only a decimal point. This should be 1807 and 15 cents, for example. You only say and where there is a decimal point. So Sammy, put that on the list for Microsoft. They need to fix that. And is only a decimal point. Okay, so let's put a code.
In fact, what I'm going to do is just so we don't have to keep typing numbers in here, let's put a default value and let's do 1865.
Okay, save it, close it, open it. All right, we got a default value. So the first step is let's set up a function. We'll do it down here, public function, it's our own function. We're going to call it format year, format, format year. We'll send to it Y as a long and we're going to return a string that the speech synthesis can say. The first thing I want to do is check to make sure that Y is within normal parameters. If it's less than a thousand, we can't handle it. And if it's greater than 9999, we can't handle it based on the logic we're setting up. So we'll say if Y is less than 1000 or Y is greater than 9999, then we'll just return the value. And then, and the speech synthesizer, we'll just say it. If you send it the year 852, it'll just say 852, okay. So format year equals, we're going to convert that Y to a string because we've got to return a string. So it's CStr(Y) and then exit function. And we'll put some comments in here. Let's add a bit of commenting. Back for valid year, okay.
Yeah, see, I told you I probably missed one or two videos. Type conversion functions, convert between data types. You got a long, you want to make it a string, you got a date, you want to make it something. That's what all these functions do. Go watch this video.
All right, now if we get down here, then we have a valid year. So let's put that in a string. So I'm going to dim S as a string and I'm going to say S = CStr(Y). Kind of what we did a minute ago, okay. In fact, it's probably not a bad idea to put this first. So let's cut that out. Let's put it up here, all right, put our dim. I'm of the habit of putting all my dims up top. I've been trying to put them down with the declare, but yeah. All right. So we already have an S here, so we don't need to call that CStr twice. We can just say that, right, S = CStr(Y).
Okay, so now here we can split S into two-digit pairs. So we're going to say S = the left of S, 2, and the space, and the right of S, 2. And yes, you can have a string work on itself like that. All right, because this starts off as 1, 9, 7, 2, and then this will be, it'll turn it into 1, 9, a space, and then 7, 2. If you don't trust me, you can say here, well, we're just going to return it. So return the value and we'll say format year equals S, okay. Save that. And now we're going to speak format year, speak this, got it. Okay, let's see what happens. And you know what, let's status it too, because we got our status box here. Let's status it too, so we can see it. So we'll put in here status S as well. All right, in fact, let's do this here. Let's say Y dash S, we can see what it came in as and what it's going out as. All right. Debug compile and let's do, go, 1865. Okay, there we go, we split it, right, came in as 1865 and now it changed it to 18 space 65. All right, so what if we got this, 1805, what happens?
1805. I don't want it to say zero, I want it to say O5, O5. All right, so back in here, let's put each of these parts into its own variable. All right, let's go left 2 as string and right 2 as string. Okay, and right here we'll say left 2 equals this, right, and we'll say right 2 equals that. So now we've got them in separate variables, easier to work with, right, we'll just replace that with this and that with this.
But now it shouldn't. We should never have the left one here being a zero, because it would fail that rule, but this could be a zero. Okay, so now we're going to replace a zero with O like that. Okay, because this will make it actually say O, like O. So if the left of right 2, 1, equals zero, remember it's a string, so you can't just put a zero there, you got to put it inside of quotes, then we're going to say right 2 equals O and a space, and the right character from right 2, 1. So grab the five or the seven or whatever that second digit is, okay, and then we'll end up, that could be one line, but that looks good. Okay, save it, come back out now and hit the button.
Okay, yeah, 1805, that's what's being spoken about 2023, let's see what that does.
2023. Yeah, sounds good, 2003, 2003, yeah, sounds great. Now there are some other exceptions too you might want to throw in here, like this, 2000, 2000 doesn't sound right, or even like 1900, 1900, right, so we'll talk about those exceptions tomorrow in part two. So tune in tomorrow, same bat time, same bat channel, or if you're a member, you can watch it right now, because I'm going to keep recording until I'm done. But that is going to be your TechHelp video for today, I hope you learned something. Live long and prosper, my friends, I'll see you tomorrow for part two.
A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They are manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
TOPICS:
- Breaking up a year into two-digit number pairs
- Handling text-to-speech for four-digit numbers
- Setting up speech synthesis in Access
- Writing a VBA function to format years
- Converting integers to strings in VBA
- Ensuring valid year range within specific limits
- Splitting a number into left and right string parts
- Replacing zero with 'O' in speech output
- Testing various year formats in text-to-speech
- Creating and using custom VBA functions
- String concatenation and manipulation in VBA
COMMERCIAL:
In today's video, you will learn how to make your Access database speak years correctly using text-to-speech. I'll show you how to format four-digit numbers into two pairs, ensuring years like 1865 are pronounced as "eighteen sixty-five" instead of "one thousand eight hundred sixty-five." We'll use some VBA coding and touch on string functions like left, right, and mid. You'll also see how to handle years with leading zeros, making sure 2007 sounds right. Watch the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What was Rachel's main issue with text-to-speech in her Access database?
A. The text-to-speech was not functioning at all.
B. It was not properly vocalizing years like 1865.
C. It was speaking too fast.
D. The voice was too robotic.
Q2. How does the text-to-speech engine read years from the 1900s to 2100s?
A. As individual digits, like 1-9-7-2.
B. Correctly as years, for example, 1972.
C. By spelling out each digit, like one-nine-seven-two.
D. It skips these years entirely.
Q3. Before attempting to implement the solution, which video does Richard recommend watching?
A. Text-to-speech video for Access databases.
B. Video on how to create graphs in Access.
C. Video on database relationships.
D. Video on creating forms.
Q4. What VBA functions are mentioned as important to understand for this tutorial?
A. Len, SubStr, For.
B. Append, Replace, If.
C. Left, Right, Mid.
D. Sum, Average, Min.
Q5. Where can the speech synthesis code be found according to the video?
A. In the Access manual.
B. In the code vault on Richard's website.
C. In the Access support forums.
D. In the VBA library reference guide.
Q6. What should you do if the year provided is less than 1000 or more than 9999?
A. Raise an error.
B. Return the year as it is.
C. Convert the year to the current year.
D. Replace it with a default year.
Q7. What string function converts a number to a string in VBA?
A. CInt
B. CDbl
C. CStr
D. CLng
Q8. What does the function 'Left' do in VBA?
A. Returns the rightmost characters of a string.
B. Removes the leftmost character of a string.
C. Returns the leftmost characters of a string.
D. Adds characters to the left side of a string.
Q9. How was the year "1807" incorrectly pronounced by the text-to-speech engine?
A. It was pronounced with an extra "and" as 18 and 07.
B. It was spoken correctly without any errors.
C. It skipped the number entirely.
D. It pronounced each digit separately.
Q10. What solution is proposed to handle years where the rightmost two digits start with zero (e.g., 1805)?
A. Leave it as is and accept the output.
B. Convert the zero to "Oh" followed by the remaining digit.
C. Remove the zero completely.
D. Add a space after the zero.
Q11. What does Richard indicate should be fixed by Microsoft regarding pronunciation?
A. The inclusion of the word "and" in number pronunciation.
B. The speed of speech.
C. The tone of voice.
D. The accuracy of mathematical calculations.
Answers: 1-B; 2-B; 3-A; 4-C; 5-B; 6-B; 7-C; 8-C; 9-A; 10-B; 11-A
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.
|
