Number Pairs in Microsoft Access

How to Break Years Up Into Two-Digit Number Pairs for Speech Synthesis in Microsoft Access

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.

1. See, it says that one right. If you make this 1813, watch what you get.

2. Okay, see, yeah. About 2024, let's see.

3. Okay. How about 2124.

4. 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?

5. Yeah, see, some people want to hear 2007. So or 1907. What's 19 do?

6. 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.

7. 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?

1. 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.

1. 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.

For a complete video tutorial on this topic, please visit https://599cd.com/NumberPairs