Round Up Cents
By Richard Rost
11 months ago
Automate Penny Rounding in Microsoft Access In this Microsoft Access tutorial, I will show you how to ensure every transaction rounds up to the next cent, just like a Ferengi. We'll cover two methods: rounding in a query without VBA and creating a VBA function to streamline your calculations. Perfect for businesses handling small transactions. Kimberly from Chino Hills, California (a Platinum Member) asks: In my business, we deal with a ton of small transactions, and 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? MembersThere is no extended cut, but here is the file 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!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, round up pennies Access, Ferengi business strategy, Office Space penny scheme, query method rounding, VBA rounding function, Access rounding up, currency formatting Access, form field rounding, advanced query design Access, negate values Access, integer rounding Access
Subscribe to Round Up Cents
Get notifications when this page is updated
Transcript
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.
Quiz
Q1. What is the main purpose of rounding up pennies in this tutorial?
A. To save money for the customers
B. To ensure the business does not lose money on small transactions
C. To comply with legal accounting practices
D. To align with competitors' pricing strategies
Q2. Which movie and TV series were mentioned in the tutorial as examples?
A. The Matrix and Doctor Who
B. Office Space and Star Trek
C. Lord of the Rings and Game of Thrones
D. Inception and Battlestar Galactica
Q3. What method is NOT used for rounding in this tutorial?
A. Using VBA to write a function
B. Using the int function in a query
C. Using the fix function directly
D. Using an alias for easier reference
Q4. Why does the instructor suggest watching a prior video about rounding in Microsoft Access?
A. To learn how to use the int function effectively
B. To learn how to set up alias names in queries
C. To understand VBA syntax better
D. To examine alternative database software options
Q5. What is the strategy used to always round up to the next cent?
A. Using a simple round function on negative numbers
B. Multiplying, negating, and using the int function, then reverting
C. Applying the bankers rounding method
D. Directly using the floor function in Access
Q6. Which of the following represents the formula used to round up to the nearest cent in the tutorial?
A. Round(C * 100) / 100
B. Negative int of (Negative C * 100) divided by 100
C. Fix(C * 100) / 100
D. Absolute(C * 100) / 100
Q7. What is one benefit of using a VBA function for rounding, as mentioned in the tutorial?
A. It provides faster computation than a query
B. It eliminates the need for manual database updates
C. It allows for a single, reusable rounding function
D. It automatically integrates with other Office applications
Q8. In the tutorial, how is the credit limit field aliased in the query for simplicity?
A. CL
B. CrLimit
C. C
D. Limit
Answers: 1-B; 2-B; 3-C; 4-A; 5-B; 6-B; 7-C; 8-C
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 focuses on rounding up penny fractions in Microsoft Access. We want to ensure that every transaction rounds up to the nearest cent so the business retains its profits. This means we'd never round down; instead, you'd round fractions up.
Kimberly from Chino Hills, California, raised an insightful query about handling numerous small transactions in a business. The concern is that tiny fractions of a penny, if not rounded up, could accumulate over time, leading to significant financial losses. While Kimberly drew parallels with Office Space and Superman 3, where the fictional schemes involved skimming pennies, her objective is akin to a prudent Ferengi from Star Trek—accounting for every bit of profit.
Let's explore how Microsoft Access can help you achieve this task. I appreciate your references, Kimberly, as they align with some of my favorite cultural touchpoints. You're a dedicated platinum member, so your question takes precedence.
There are two solutions: one is using a query method without VBA, and the other involves creating a VBA function. I will guide you through both, but let's start with the beginner-friendly query approach, especially as Kimberly hasn't delved into developer classes yet.
First, you must understand the basics of rounding in Access. Access typically rounds displayed currency values, similar to Excel, so a displayed number might not reflect its true stored value. For example, a value entered as $100.015 might display as $0.02 due to rounding but still stores the precise value.
To see how this works in practice, I used my TechHelp free template, available for download on my website. In this template, I have a Customer table where we'll focus on the 'credit limit' field. Creating a query involves bringing the Customer table into the design view and setting an alias for the 'credit limit' to 'C' to simplify further operations.
If you have a value like $100.015, Access might display a rounded $0.02 without changing the actual value. To reveal this, adjust the Decimal Places property in the query to show more decimal values, ensuring clarity in how the numbers appear.
Rounding up involves several steps. The process comprises multiplying the value by 100, negating it, applying the Int function to push it further negative, then flipping it back and dividing by 100 to get the final rounded value. Though appearing complex, this process assures rounding up to the next penny.
For convenience, I've illustrated everything in separate steps, but you can condense this into a single formula. Using it, you can achieve the same rounding without sequential operations.
Furthermore, this calculation can be implemented in a form field or a VBA function, which offers a more centralized method to apply rounding across queries and forms. The advantage of using VBA is that the function can be called consistently, eliminating redundancy.
I'll cover how to set up this rounding process in a VBA function in the follow-up session on Monday. If you're a member, you have early access to this tutorial.
To recap, ensure you watch the complete video tutorial on my website for a detailed walkthrough of everything discussed here today. Live long and prosper, my friends.
Topic List
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
|
