Uploaded November 2026 | Updated November 2026, 4 days ago
In this lesson, we will show you two VBA methods for adjusting prices ending in 9 in Microsoft Access without increasing the original value. You will learn how to use a loop that subtracts pennies until the final digit is 9, and how to use a mathematical shortcut with rounding, the Mod operator, and the Int function. We will also cover handling minimum-price edge cases.
Mason from Burlington, Vermont (a Gold) asks I run a small retail store, and every month I get an updated price list from one of my suppliers. I usually mark everything up about 10%, but my boss likes all our selling prices to end in 9, like $19.99 or $8.49. He says customers like those prices better. Right now, I'm going through and adjusting them by hand, which is a pain. Is there an easy way to have Access do this for me automatically?
Silver Members & up get access to an Extended Cut of this video. In the extended cut, we will apply the price-ending-in-9 adjustment to a table of products using queries. I'll show you how to calculate markup from product costs, preview the adjusted retail prices, and update the table. We will first use functions across multiple records, then do the price adjustment directly in SQL for faster processing of large recordsets.
MEMBERS-ONLY EXTENDED CUT VIDEO:
youtu.be/290oOPOuK8w
CHAPTERS:
00:00 Introduction
02:20 Prerequisites
02:53 Setting Up the Price Adjustment Button
03:24 Creating the Loop Function
05:53 Using Mod to Find the Last Cent Digit
08:41 Testing the Loop Method
09:13 Mathematical Shortcut Overview
11:10 Creating the Math Function
13:11 Testing Edge Cases
14:30 Extended Cut Preview
15:57 Conclusion
BECOME A MEMBER:
YouTube: youtube.com/channel/UCl7-jJie4ifOVK87FINIuDg/join
or My Site: 599cd.com/THMember
LEARN MORE:
Prices Ending in 9: 599cd.com/PricesEndingin9
PREREQUISITES:
Intro to VBA: 599cd.com/IntroVBA
Mod: 599cd.com/Mod
Rounding: 599cd.com/Round
Custom Functions: 599cd.com/Function
LINKS:
Update Queries: 599cd.com/UpdateQuery
Markup and Margin: 599cd.com/Markup
RECOMMENDED COURSES:
Access Developer 1: 599cd.com/ACD1
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
DIAMOND SPONSORS:
More info on our Sponsors: 599cd.com/DiamondSponsors
ADDITIONAL RESOURCES:
FREE Access Beginner Level 1: 599cd.com/Free1
How to Use Access in 30 Minutes: 599cd.com/30Minutes
$1 Access Level 2: 599cd.com/1Dollar
Donate to my Tip Jar: 599cd.com/TipJar
Merch Store! Get your swag here: 599cd.com/Merch
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: twitter.com/LearningAccess
en.wikipedia.org/wiki/Microsoft_Access
products.office.com/en-us/access
microsoft.com/en-us/microsoft-365/blog/access
KEYWORDS:
TechHelp, Access 2016, Access 2019, Access 2021, Access 2024, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Access VBA price ending in 9, price rounding to .99, psychological pricing, VBA custom function, VBA Mod operator, VBA Int function, round currency values, SQL price update query, retail price markup, bulk price adjustment
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Live long, and prosper.
In this lesson, we will show you two VBA methods for adjusting prices ending in 9 in Microsoft Access without increasing the original value. You will learn how to use a loop that subtracts pennies until the final digit is 9, and how to use a mathematical shortcut with rounding, the Mod operator, and the Int function. We will also cover handling minimum-price edge cases.
Mason from Burlington, Vermont (a Gold) asks I run a small retail store, and every month I get an updated price list from one of my suppliers. I usually mark everything up about 10%, but my boss likes all our selling prices to end in 9, like $19.99 or $8.49. He says customers like those prices better. Right now, I'm going through and adjusting them by hand, which is a pain. Is there an easy way to have Access do this for me automatically?
Silver Members & up get access to an Extended Cut of this video. In the extended cut, we will apply the price-ending-in-9 adjustment to a table of products using queries. I'll show you how to calculate markup from product costs, preview the adjusted retail prices, and update the table. We will first use functions across multiple records, then do the price adjustment directly in SQL for faster processing of large recordsets.
MEMBERS-ONLY EXTENDED CUT VIDEO:
youtu.be/290oOPOuK8w
CHAPTERS:
00:00 Introduction
02:20 Prerequisites
02:53 Setting Up the Price Adjustment Button
03:24 Creating the Loop Function
05:53 Using Mod to Find the Last Cent Digit
08:41 Testing the Loop Method
09:13 Mathematical Shortcut Overview
11:10 Creating the Math Function
13:11 Testing Edge Cases
14:30 Extended Cut Preview
15:57 Conclusion
BECOME A MEMBER:
YouTube: youtube.com/channel/UCl7-jJie4ifOVK87FINIuDg/join
or My Site: 599cd.com/THMember
LEARN MORE:
Prices Ending in 9: 599cd.com/PricesEndingin9
PREREQUISITES:
Intro to VBA: 599cd.com/IntroVBA
Mod: 599cd.com/Mod
Rounding: 599cd.com/Round
Custom Functions: 599cd.com/Function
LINKS:
Update Queries: 599cd.com/UpdateQuery
Markup and Margin: 599cd.com/Markup
RECOMMENDED COURSES:
Access Developer 1: 599cd.com/ACD1
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
DIAMOND SPONSORS:
More info on our Sponsors: 599cd.com/DiamondSponsors
ADDITIONAL RESOURCES:
FREE Access Beginner Level 1: 599cd.com/Free1
How to Use Access in 30 Minutes: 599cd.com/30Minutes
$1 Access Level 2: 599cd.com/1Dollar
Donate to my Tip Jar: 599cd.com/TipJar
Merch Store! Get your swag here: 599cd.com/Merch
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: twitter.com/LearningAccess
en.wikipedia.org/wiki/Microsoft_Access
products.office.com/en-us/access
microsoft.com/en-us/microsoft-365/blog/access
KEYWORDS:
TechHelp, Access 2016, Access 2019, Access 2021, Access 2024, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Access VBA price ending in 9, price rounding to .99, psychological pricing, VBA custom function, VBA Mod operator, VBA Int function, round currency values, SQL price update query, retail price markup, bulk price adjustment
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Live long, and prosper.










