Uploaded August 2026 | Updated September 2026, 2 weeks ago
In this lesson, I will show you how to chop off decimals in Microsoft Access without rounding the stored value. We will create a query that uses the Fix function to display only the whole-number portion of a currency or numeric field, while keeping the full value in the table. You will also see how to use the Got Focus and Lost Focus events to switch a textbox between the editable original field and the truncated calculated field.
Daniel from Portland, Maine (a Gold Member) asks I have an Access form with account balances that need to keep their exact cents in the table, but I don't want to see pennies all the time. When I set the display to zero decimal places, 299.99 shows as 300. Can I normally show 299, but still click the field to see and edit the full 299.99?
Silver Members & up get access to an Extended Cut of this video. In the extended cut, we will learn how to make the Fix-display swap reusable for multiple numeric fields by creating a generic function, so you do not need separate Got Focus and Lost Focus procedures for every textbox.
MEMBERS-ONLY EXTENDED CUT VIDEO:
youtu.be/B2ITRkFLA7o
CHAPTERS:
00:00 Introduction
01:28 Rounding vs. Truncating Account Balances
03:28 Why Number Formatting Won't Work
07:10 Prerequisites
08:15 Form Display Walkthrough
09:35 Creating a Truncated Query Field
11:46 Switching Control Sources for Editing
15:00 Extended Cut Preview
16:11 Conclusion
BECOME A MEMBER:
YouTube: youtube.com/channel/UCl7-jJie4ifOVK87FINIuDg/join
or My Site: 599cd.com/THMember
LEARN MORE:
Chop Off Decimals: 599cd.com/ChopOffDecimals
PREREQUISITES:
Rounding: 599cd.com/Round
Calculated Fields: 599cd.com/Calculated
Format Currency: 599cd.com/FormatCurrency
Intro to VBA: 599cd.com/IntroVBA
On Got Focus: 599cd.com/OnGotFocus
LINKS:
Format: 599cd.com/Format
Forms Bound to Queries: 599cd.com/FormsBoundtoQueries
Can't Edit: 599cd.com/CantEdit
RECOMMENDED COURSES:
Intro to VBA in Access Developer 1: 599cd.com/ACD1
Account Balances Template: 599cd.com/Balances
Focus Events in Access Advanced 3: 599cd.com/I15A5
Calculated Query Fields in Access Expert 10: 599cd.com/ACX10
Math Functions in Access Expert 26: 599cd.com/ACX26
Form Controls in Access Expert 4: 599cd.com/ACX4
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 truncate decimals without rounding, Access Fix function, Access textbox ControlSource, Access Got Focus event, Access Lost Focus event, editable calculated field, Access continuous form, truncate currency cents, Access calculated query field, decimal places rounding
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Live long, and prosper.
In this lesson, I will show you how to chop off decimals in Microsoft Access without rounding the stored value. We will create a query that uses the Fix function to display only the whole-number portion of a currency or numeric field, while keeping the full value in the table. You will also see how to use the Got Focus and Lost Focus events to switch a textbox between the editable original field and the truncated calculated field.
Daniel from Portland, Maine (a Gold Member) asks I have an Access form with account balances that need to keep their exact cents in the table, but I don't want to see pennies all the time. When I set the display to zero decimal places, 299.99 shows as 300. Can I normally show 299, but still click the field to see and edit the full 299.99?
Silver Members & up get access to an Extended Cut of this video. In the extended cut, we will learn how to make the Fix-display swap reusable for multiple numeric fields by creating a generic function, so you do not need separate Got Focus and Lost Focus procedures for every textbox.
MEMBERS-ONLY EXTENDED CUT VIDEO:
youtu.be/B2ITRkFLA7o
CHAPTERS:
00:00 Introduction
01:28 Rounding vs. Truncating Account Balances
03:28 Why Number Formatting Won't Work
07:10 Prerequisites
08:15 Form Display Walkthrough
09:35 Creating a Truncated Query Field
11:46 Switching Control Sources for Editing
15:00 Extended Cut Preview
16:11 Conclusion
BECOME A MEMBER:
YouTube: youtube.com/channel/UCl7-jJie4ifOVK87FINIuDg/join
or My Site: 599cd.com/THMember
LEARN MORE:
Chop Off Decimals: 599cd.com/ChopOffDecimals
PREREQUISITES:
Rounding: 599cd.com/Round
Calculated Fields: 599cd.com/Calculated
Format Currency: 599cd.com/FormatCurrency
Intro to VBA: 599cd.com/IntroVBA
On Got Focus: 599cd.com/OnGotFocus
LINKS:
Format: 599cd.com/Format
Forms Bound to Queries: 599cd.com/FormsBoundtoQueries
Can't Edit: 599cd.com/CantEdit
RECOMMENDED COURSES:
Intro to VBA in Access Developer 1: 599cd.com/ACD1
Account Balances Template: 599cd.com/Balances
Focus Events in Access Advanced 3: 599cd.com/I15A5
Calculated Query Fields in Access Expert 10: 599cd.com/ACX10
Math Functions in Access Expert 26: 599cd.com/ACX26
Form Controls in Access Expert 4: 599cd.com/ACX4
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 truncate decimals without rounding, Access Fix function, Access textbox ControlSource, Access Got Focus event, Access Lost Focus event, editable calculated field, Access continuous form, truncate currency cents, Access calculated query field, decimal places rounding
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Live long, and prosper.
