Math in Fields
By Richard Rost
3 years ago
Entering Math Equations in Access Like Excel
In this Microsoft Access tutorial I'm going to teach you how to enter math equations into form fields just like you can in Excel cells. This way you can type in things like 5+5+7 or (2*10)+2 directly into a field and have Access evaluate it. It's going to involve a little bit of VBA code but it's not that hard.
Pre-Requisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Eval Function, Can Access do formulas like Excel, How do you enter equations in Access, Learn to build an expression, How do I use formulas in Access
Subscribe to Math in Fields
Get notifications when this page is updated
Intro In this video, I will show you how to enter math equations directly into form fields in Microsoft Access, similar to the way you can in Excel. You'll learn how to set up an unbound text field for user input, use VBA to evaluate math expressions, handle errors gracefully, and strip out the leading equal sign for Excel-style entries. This method makes data entry easier for users who are used to entering calculations on the fly without needing to do the math themselves.Transcript Welcome to another Fast Tips video brought to you by accesslearningzone.com. I am your instructor Richard Rost and today's video is going to show you how you can enter math equations into your Microsoft Access form fields just like you can in Excel. Today's video is going to be a developer-level video that's going to have a little bit of programming in it.
One thing that I do love about Excel is that you can type equations directly into cells. For example, let's say you're doing your daily bank account balance. You type in whatever your daily balance is. Today it's 1,200 or 12,000. Fine, I'll take that. Then you have four or five pending items. Some of my bank accounts show you the pending total, but others don't. That's one of my pet peeves with Amazon. On my Amazon credit card, they do not total up the pending transactions, which drives me nuts. So then you have to either open up your calculator or figure it out in your head.
I just want to be able to do this: I want to say, I have a $12 charge coming out, so minus $12, a $22 charge, $35 charge, and then maybe a $15 payment. I'll type in something like that right into the pending field. Press Enter. Excel does the work for you.
You can't do that in Access. If you've got a numeric field, like let's say family size, it's a number. Same thing with currency value; it doesn't matter. If I come over here to family size, and I type in 2 plus 2, you get an error message. Not valid for this field. You have to type in an actual number. So I want to be able to set up a field where I can just come in here and type in minus 5 minus 6 or plus 6, plus 69 plus 4 plus 1, Enter, and it calculates that for me.
Unfortunately, there is no built-in way to do that in Access, but we can do it with a little bit of programming. I say a little bit because this is a fast tip, so it's not going to be too much programming. If there was a lot of programming, I would make it a developer lesson out of it. But let me show you how to do it.
Now, this is one of those things that's not terribly hard to do if you have the background and the knowledge for it. In order to do this, it's not a lot of code. It's only maybe 5 or 10 lines of code, but you have to know a lot of the building blocks first.
First of all, if you've never done any VBA programming before, go watch this video. It's about 20 minutes long. It will teach you everything you need to know to get started.
Then you're going to have to know how some basic events work. There is an After Update event. Go watch this video. This is free, by the way. These are all free videos on my website and my YouTube channel. I'll put links down below you can click on. After Update fires when you change a value, so go watch that one. Go watch this video on the On Current event. This one runs when you move from record to record.
Optionally, here are some other ones for you. You should know what Null means and how to use the IsNull function. We're also going to do a little basic error handling with On Error Resume Next, so go watch this. We'll use an If Then clause optionally. You can get away without this one, but if you want to learn about If Then statements, go watch this one. Finally, go watch my string functions video. We're going to use the Left, Right, and the Length string functions in this video.
Here we go.
Now, for you more advanced programmers, I tried playing around with doing this with just one field, with both the Before Update event for the family size field and the Form Error event. Neither one of those would work properly. I couldn't get it to work. If you can do this without creating a second field, I want to know about it, so let me know. My solution is to add a second unbound field. We're going to call it family size text.
The problem is that if it's a numeric field bound to a number field or a currency field in the table (when I say number, I also mean currency), if it's bound to that type of field in the table, you have to enter a numeric value. You can't get away with putting in the pluses, minuses, or parentheses. We're going to have to use a text field to do our data entry, which will also be what we'll see on the screen, but we'll still save the data in that numeric field. We'll just hide it.
The first thing is to add a text field for this family size. I'll call it family size text. I just copy and paste this, put it up here, and delete that label. I'm going to shrink this guy down like that. This will be the hidden one. This will be the text value. We're going to rename this guy. We're going to call this family size text, and we're going to get rid of the control source. It can't be bound to anything in the table. That's the number value. We're going to hide this one eventually.
Right now, let's do white text with a red background. There we go.
This guy is what we're going to do the data entry into, but we also need to see what the value is in there. To do that, we're going to use the After Update and the On Current event. When this thing is updated, well, this guy should never actually really be updated, so really, On Current should work. But I'm going to put it in there anyway, just in case you decide you want to make this visible later on.
Go to Events, go to After Update. Now we're going to make our own subroutine because we're going to be calling it from two locations. It's going to be called update family size text. We're going to update the text field. Where is that going to go? Right up at the top of your private sub update family size text. All that is going to do is it's going to say family size text equals whatever is in family size. That's it.
Now we can't just call it from the After Update. We also have to call it from the Form's On Current event. So right here, Form On Current, we're going to copy and paste that.
If we update family size, it will update the text field. As we move from record to record, it will also update that text field. Save it, close it, and come back in. You can see right there, as I move from record to record, it's updating. If you change this, it will also update. I'm not too worried about that because we're going to hide this eventually, but again, I'm leaving it in case you want to leave it visible.
While I'm also in here, I'm going to update the tab order. So you go tab here, tab to there. Save it, close it.
Now we have to go the opposite direction. The user is actually going to be typing the value into here. When they do, I need to evaluate what they type in and save that value over here. How do we do that? There is something called the Eval function. Let's see how that works.
We're going to go into this guy's After Update event as well. This happens when the user updates this guy. We're going to save family size equals eval(family size text). The Eval function basically takes a string value and evaluates it mathematically and converts that over to a number value. Save it, and let's take a look at what we get.
I'm going to come over here and I'll type in 2 plus 2 and hit tab. Boom. There's my four. See that? Pretty cool. Minus 8 minus 8 minus 5. Boom, negative 21. Beautiful. You can also put in things like parentheses (4+5)*9. As long as it's a valid math equation, Access will evaluate it for you.
Now, when that happens, we don't want to actually see that there. We want this to update itself. So after that runs, we're then going to also say family size text equals family size, to evaluate it and then set me to the calculated version as well. If I come in here and I say plus 2 plus 2, boom. They both change to four, because remember, this will be hidden. Minus 8 minus 5 minus 1. Boom, negative 14.
Next up, we have to handle null values because if you come in here and delete this and press tab, we get an invalid use of null. Let's end that.
What we can do is check for the null value. We could come in here and say if IsNull(family size text) then family size equals zero else do that. End if. That's one way we could handle it, and that will work for the null values. So if I come in here and put down a three and then I come back in here and delete it, I get my zero. So that's the line.
But now we also have to handle a situation where they type in something that's not valid at all, like they type in "poop." It can't find the name "poop" you are trying to evaluate. It's trying to find a field name or something but it has no idea what "poop" is. So we need a more robust way of checking for any time they enter something that Access can't evaluate. For that, we'll use one of my favorite things and least favorite things, which is On Error Resume Next.
You have to be very careful using this. Don't use it in every situation, but in this situation, it's going to work just fine. We can actually get rid of the If Then statement like this. I'm just clicking there and hitting Control Y. Control Y, by the way, Michael, is to cut the line. I've been doing that for years.
We're going to say right here On Error Resume Next. What that says is if you encounter an error, just ignore it and move on to the next line. So it's going to set family size to zero, and then it's going to try to evaluate family size text. If it's successful, family size will get a new value. If not, it's going to ignore it and continue on to the next line, in which case, family size is zero.
Now, if I come out here and I type in something like 5 plus 5, I get my 10. If I type in 8, I get my 8. If I type in "poop," I get a zero. If I type in a null, I get a zero. See how that works? You have to be careful with this. I talk about this a lot more in my error handling and debugging video, but if you have a big long list of commands and you throw an On Error Resume Next up top, and any one of those lines could be causing an error and you don't know about it, when you're developing your database, sometimes you want to know what that error is so you can handle it or fix it.
Don't just use this everywhere; I use it very sparingly for very small bits of code. If there was a lot more stuff after this, I would turn error handling off after it with On Error GoTo 0. That puts error handling back on the way it was. Here, it's a tiny subroutine, so that's all we need.
One more thing I thought about when I was running through this before is that if you have people who are used to Excel, and that's part of the reason why we're doing this (I use this a lot when I do my daily or weekly check on the credit card balances), like I said, some websites do and some don't give you that pending total. I just want to type in 9 plus 8 plus 1 or whatever. I don't want to have to do math in my head, especially because I do that first thing in the morning before my coffee has kicked in.
What I was trying to say is, if you have people who are used to Excel, they might be used to starting off with an equal sign, like =5+9+6. Why is that? Because Access is handling that as an equality; it's trying to say, "is this equal to" and it's returning a Boolean value, which in this case is always going to return a false. So what we need to do if they start with an equal sign here is just chop it off. To do that, we'll look at the left one character and say if that's an equal sign, remove it.
A little bit more complicated line of code, but we'll come right here. We'll say if left(family size text, 1) equals an equal sign, then family size text equals the right(length of family size text minus 1) characters. Not the whole rest of the string from the right side. So it's going to be the right of family size text, length of family size text minus one. If it's 10 characters long, we want the right nine characters.
Save it, and this should do it. So if I come in here and say 5 plus 5, there's my 10. I say plus 9 plus 9, I get my 18. If I say =7+7, I still get my 14 because it chops that equal sign off for us.
Now all we have to do is hide this guy, but be careful because if you hide this, it's also going to hide the attached label. What I'm going to do is chop this label off (Ctrl+X), click on that guy, and paste (Ctrl+V) to just reassign the label to the new field. Now this guy can be slid over here, open it up, and let's go to format, visible, no.
I have a whole video on the visible property if you want to go watch it. I assume if you're watching this, you know how to use visible.
Now that guy won't be seen. Save it, close it, open it up, and now the user is none the wiser. Boop. And there we go.
If you like stuff like this and you learned something today and you're having fun learning with me, I have tons and tons of hours more of developer lessons on my website. Check them out. There's a link right there. I'll put a link down below. I cover stuff in the order that you need to learn it. I do a little bit of this, then a little bit of that, and we have fun. But that, folks, is your fast tip for today. I hope you learned something. We'll see you next time.Quiz Q1. What is the main goal of the tutorial in this video?
A. To connect Access with Excel for advanced calculations
B. To allow math equations to be entered into Access form fields like in Excel
C. To create a currency converter in Access
D. To design a banking application in Excel
Q2. Why can't you enter equations like "2+2" directly into a numeric field in Access?
A. Access only accepts alphabetic values in numeric fields
B. Numeric fields in Access require a plain number value and cannot evaluate string-based math
C. Access automatically reformats equations into text
D. Numeric fields in Access perform calculations automatically
Q3. What is the solution proposed in the video for allowing users to type math equations in a form?
A. Convert all fields to text in the table
B. Use a separate unbound text field for user input and VBA code to evaluate the expression
C. Use macros to enable equation entry
D. Enable a special property on the numeric field
Q4. Which VBA function is utilized to evaluate math equations entered as string text?
A. Calculate()
B. Eval()
C. MathEval()
D. Execute()
Q5. What event is triggered when a user finishes entering a value in a form field, and the code acts on this new input?
A. On Load
B. On Click
C. After Update
D. On Focus
Q6. If the user deletes the input so the field is empty, what must the code do to avoid an error?
A. Automatically close the form
B. Insert the text "empty"
C. Check for null with IsNull and set the value to zero
D. Restart the application
Q7. How does the code handle invalid input, such as typing a word instead of a math equation?
A. It crashes Access
B. It shows a detailed error message
C. It uses On Error Resume Next to avoid breaking and sets the value to zero
D. It highlights the input red
Q8. Why is it necessary to remove a leading equal sign if the user includes it in their input?
A. The equal sign is required for string fields only
B. Access will interpret it as a Boolean expression, resulting in a false value
C. It formats the output as exponential notation
D. It causes Access to change the field format
Q9. What string functions are used to remove the equal sign from the user's input?
A. Left, Right, and Length
B. Mid, Trim, and Replace
C. Search, Find, and Substitute
D. Prefix, Suffix, and Count
Q10. Why is On Error Resume Next used sparingly according to the instructor?
A. It makes the code slower
B. It can hide useful errors during development
C. It is required in all VBA routines
D. It is deprecated in newer versions of Access
Q11. After processing the equation, what should happen to the visible fields on the form?
A. Both input and output should remain visible
B. Only the calculated value (numeric field) should remain visible
C. Both fields should be hidden
D. The label should be deleted
Q12. What is the benefit of using this math entry technique for people used to Excel?
A. They can enter formulas as they are accustomed without needing separate calculation tools
B. It forces them to learn manual math
C. It merges all fields into one large field
D. It converts all data into percentages
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-C; 7-C; 8-B; 9-A; 10-B; 11-B; 12-A
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 video from Access Learning Zone covers how you can enter mathematical equations directly into Microsoft Access form fields, similar to how Excel lets you type formulas into its cells. This is a feature that many people enjoy in Excel, especially for tasks like keeping track of daily bank account balances. In Excel, you can type equations such as subtraction of several transactions directly into a cell, and it will calculate the result for you on the spot. Access, however, expects you to enter only numeric values into number or currency fields and gives an error if you try to enter equations like 2 plus 2.
I wanted to be able to do exactly what I do in Excel within Access forms: enter a basic expression like minus 5 minus 6 or plus 69 plus 4 plus 1, hit Enter, and have Access evaluate it to give the answer. Unfortunately, Access does not support this out of the box, but with a bit of VBA, we can make it possible.
This tip requires some developer skills, but it is not an overwhelming amount of programming. If you are new to VBA, I recommend watching my introductory VBA tutorial, which explains the basics needed to get started. You will also need to understand some common events in Access such as After Update, which occurs when a field's value changes, and the On Current event, which fires when you move from one record to another. I have free tutorials on each of these topics on my website and YouTube channel, so you can brush up on those concepts if you need to.
In addition, it helps to know about the use of null values and the IsNull function, some basic error handling with On Error Resume Next, and a bit about If Then statements. We'll also make use of string functions like Left, Right, and Len to manipulate text values.
For this project, the main issue is that Access form fields bound to numeric or currency fields require plain numbers, not formulas or equations. To solve this, I add a second, unbound text field to the form - for example, family size text - which will be used to accept the equation input from the user. The bound numeric field (the one linked to your database table) will be hidden from users, and data entry will happen through the new text field. This field will display the result of the equation after it is evaluated, and its value will be saved back to the numeric field in your table.
To begin, add the unbound text field to your form, setting its name appropriately. This will be the field users interact with. The bound numeric field will be visible only to your VBA code, and in practice, you will set it to hidden on the form.
You need to synchronize the values between the text entry field and the numeric field. This is done using the After Update event on the bound field, as well as the Form's On Current event. Both events will call a subroutine, let's say update family size text, which simply copies the value of the numeric field into the text field for display. This ensures the text field always shows the correct value as you move between records or change data.
The next step is to process and save whatever the user types into the text field and store the calculated result in the numeric field. We do this in the After Update event of the text field. This is where the Eval function comes in. Eval allows Access to interpret a string as an equation and compute the result. So, whatever the user types—for example, 2 plus 2—will be mathematically evaluated and stored as the numeric value in your database.
For example, if you type 2 plus 2 and then move away from the field, it will convert that string into 4 and store it correctly. You can even type expressions with parentheses, like (4 plus 5) times 9, and Access will evaluate them as intended.
Next, it is important to handle scenarios where the text field is empty (null) or contains invalid input. If it is null, you want the stored value to default to zero. If the user types something that is not a valid equation, such as an unrelated word, you do not want your database to display an error. To manage this, I use On Error Resume Next. This lets Access skip over errors if the evaluation fails, ensuring the numeric field is set to zero rather than showing a runtime error.
Be careful when using On Error Resume Next. It is useful for small, specific routines but can hide errors you might want to know about during development. In longer routines, always reset error handling afterwards with On Error GoTo 0.
There is one more consideration: users accustomed to Excel often start their equations with an equal sign, like =5+9+6. In Access, starting with an equal sign causes Eval to interpret the expression as a logical comparison, returning a Boolean value instead of a number. To prevent this, check if the first character in the string is an equal sign and, if so, remove it before evaluating the mathematical expression. This way, whether users start with an equal sign or not, Access processes the equation as they would expect.
Finally, once the field is working as intended, you can hide the original bound numeric field from the user while leaving the text entry field and its label visible. This creates a seamless experience for your users, who can now enter equations directly in your Access forms just like they do in Excel.
If you enjoy developer tips like this and want to continue learning with me, I have many more detailed lessons available on my website. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Setting up Excel-like math entry for Access fields
Adding an unbound text field for data entry
Syncing data between text and numeric fields
Using the Eval function to calculate entered equations
Handling null values in equation entry
Adding error handling for invalid equations
Removing the leading equals sign from input
Using string functions to process user input
Updating tab order for form usability
Hiding the unbound text field after setup |
