Today we're going to talk about rounding up those pennies so there's never a fraction of a penny. We'll make sure the customer is paying that extra little bit. So we're never going to round down; we're going to round up. Today's question comes from Kimberly in Chino Hills, California, one of my platinum members.

In my business, we deal with a ton of small transactions. While it might seem petty, we always round up to the next cent. If we didn't, those tiny fractions of a penny would add up over time, costing us real money. It's like that scheme in Office Space or Superman 3, except instead of us skimming pennies, we would be the ones losing them. We want to run our business like Shrewd Ferengi, making sure every bit of profit is accounted for. What's the best way to accomplish this in Microsoft Access?

In any case, Kimberly, I love the fact that you referenced three of my favorite things in one question: Office Space, great movie. While Superman 3 wasn't a great movie, Superman is my all-time favorite superhero. And of course, you mentioned the Ferengi, Star Trek, love it. That's why I bumped your question to the top, plus you're a platinum member.

Yes, we can definitely put together something that will round every calculation up to the next cent. Now, I'm going to show you two methods for doing this. One, I'm going to teach you how to do it straight in a query, which you can also do it in a form field if you want to, without any VBA at all. Then I'm going to show you how to write a function to do it with VBA. So you get the best of both worlds. Kimberly, I know you're not in the developer classes yet, so I'm going to teach you the beginner method first.

Now, before we get started, go watch this video first. This video teaches you all about rounding and Microsoft Access and how it normally works. I teach you about the round function, int, fix, and a thing called bankers rounding. Go watch this first because we are going to use the int function today. I'd like you to know how this works before we go over the stuff today. So go watch this, then come on back.

Here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. In here, I've got a customer table. I do most of my work in this customer table. That's why I tried to make sure there's at least one of every field in here. I've got a dollar amount right here called credit limit. We'll work with this.

Let's go make a query, create, query design. I will bring in just the customer table and we can close that. I'm just going to bring in the customer ID and that credit limit field. There we go. Now, just so I don't have to constantly refer to this thing as credit limit, I'm going to alias it. I'm going to call it C. How do you do that? I'm going to zoom in, shift F2. I'm just going to say C: credit limit. That's basically saying we're going to call credit limit C.

Now if I run this, you'll see it's just called C. That's called an alias. If you want to learn more about that, here's a video for that. I'll put a link down below. With all my other calculations, I can just refer to C instead of having to type in credit limit all the time.

If we take a look at this, you can see that we don't have any fractions of a penny in here. Let's put one in. I'll take this first one here. Let's make this $100 and .015 cents. If I hit Tab, it goes to .02. It didn't round the value; it's just displaying a rounded value. That's one of the things about Access you have to learn. It's kind of like Excel. The actual value in there might be different from what you see because Access is rounding your currencies.

We're going to go to Design View. I'm going to open up the properties for this column in the query here, this field in the query. Right-click, go to Properties. Over here, under Decimal Places, let's put in four, just to make sure we can see four decimal places. Save that. Let's call this the Round Up Query, whatever. Now if we run this, we can see all of the decimal places. We've got four decimal places. Let's do this for a couple of people here.

Let's just put this one in here: $5.25.6. $100 and .009 cents. $5 or whatever. Let's get rid of the rest of these to keep things simple here. That's what we've got. We can't just use rounding because this .015 will go to .02. Let's put one in here: .011. If we normally rounded that, it would round down to 1 cent. We don't want that.

What we do want to do is always make that value get bigger. What does bigger mean? Bigger means getting further on the number line away from 0. If you're .015, you want to go further from 0 up to .02. If we use the int function and take the negative of these numbers, it will go further away from 0. It will go to the left. But I always want to go to the left on the number line if we're negative to get bigger.

What I'm going to do is take these values. First, I'm going to multiply them by 100 so that the decimal is there. Then I'm going to negate them. Then I'm going to use the int function which will push them further negative. In other words, make the value higher. When we're all done, we'll flip that back to positive again. Here's how it works.

I know it seems like a lot of steps, but this is the best way to do it. I'm going to do it in multiple steps so you can see every step of the way what I'm doing here. The next is going to be, we're going to call it C2. We're going to see two C3. Each step of the way will be a different C.

C2 is going to be the first thing we're going to do. We're going to multiply that value by 100. It's going to be C times 100. Let's see what that looks like. That's 100 times. Now we can take the negative of that number. C3 is going to be equal to C2 times negative 1. You'll see why in just a minute.

Now we're negative. If I take the int of that number, it's going to force it to go left on the number line. That's why we had to negate it. If we didn't negate it, it would just chop off the fractions here and move it left on the number line. You'd get 525 instead of 526. But if it's negative, it's going to go left on the number line. Now, C4 is going to be the int of C3. It moves it down to the next integer below where you're at.

If I just did that to this, it would be 525. This would be 10,001. This would be 501. If we negate it first and then use int, it forces it to go down to the next integer. Any fraction, no matter how small it is, will push you down to the next integer. Now I can flip it back to where it's supposed to be. I could say C5 is C4 times negative 1. That'll make it positive again.

Now I just divide that hundred back out. So C6 is C5 divided by 100. There you go. That's taking this value and no matter what, rounding it up to the next penny. If you put in values that don't need to be rounded, like 101, you're fine. If you go 1.011, that looks good. If you do put in negative numbers, like 1.011, it is going to round that down to 1.01.

But if the purpose that you want here is to minimize or refund, then you want to refund them 1.01 and not 1.02. The whole point of this is to round up, basically. You are rounding that up and making it bigger than it should be.

Could you express all of this in one formula instead of having to do all these steps? Yes, you can. I did it this way for the purposes of illustration. Let's make a second query. Save changes. Yes. I'll leave that round up here for the members. Create another query. Let's bring in customer T again. Again, I'll bring in the customer ID and that credit limit, which I will call C.

The formula by itself would look like this. I'm going to zoom in so you can see it. We'll call it round up cents. It's going to be negative int of negative C times 100 divided by 100, which is basically all the steps that we just did. If you run it now, there you go. Same thing. It's easier to understand if I break it all down. You're not here just to get the formula on type, and you want to learn how I got to this point.

If you want, yes, you can format this as currency. Right-click properties. Format is currency. We can save this as round up to Q. There you go. Probably show the decimal places. Decimal places: 4. Save that. Close it. Close it. Round up to Q. Perfect. That's how you do the calculation in a query. You can do the same thing in a form field if you want. You can also do it in a VBA function.

The benefit of the VBA function is that you don't need to have all those calculations in your queries and in your forms. You can just call the function round up cents. I'll show you how to set that up in part two on Monday. Today's Friday. Tune in on Monday for part two. I'll show you how to set it up in a VBA function. If you remember, you can watch it right now because that's one of the benefits of being a member. You don't have to wait.

So that'll be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you on Monday for part two.

TOPICS:
Rounding up fractions of a penny
Rounding up in Microsoft Access
Creating a query without VBA
Using the int function for rounding
Multiplying values for rounding purposes
Negating values for proper rounding
Step-by-step rounding process
Creating an alias in a query
Formatting query results to currency
Rounding calculations in query design
Simplifying calculations using a single formula
Preview of a VBA function for rounding

COMMERCIAL:
In today's video, we're discussing how to ensure your small business doesn't lose money by rounding up every transaction to the next cent in Microsoft Access. We will explore two methods: one using a simple query to round up amounts to the nearest penny without any VBA, and the second method, utilizing VBA to create a custom function for even more flexibility. We're starting with the basics today to ensure everyone is on board before moving on to the more advanced VBA functionality in Part 2 coming Monday. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.