Gain or Loss
By Richard Rost
3 years ago
Identifying Gains and Losses Using IIF and ABS
In this Microsoft Access tutorial, I'll demonstrate how to identify a gain or a loss within a text string using the IIF function, ABS, and some string concatenation.
Elena from Garland, Texas (a Gold Member) asks: I currently print a monthly report for each district office, showing whether they've gained or lost subscribers. Is it possible to include phrases like "gained 3" or "lost 2" in my reports, instead of just "+3" or "-2"? I believe this would make the reports easier to read and look more professional.
Members
There is no extended cut, but here is the database 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!
Prerequisites
Links
Recommended Courses
Usage
- GoL: Switch(IsNull([Amt]),"No Data",[Amt]=0,"No Change",[Amt]>0,"Gained " & [Amt],[Amt]<0,"Lost " & [Amt]*-1)
Keywords
TechHelp Access 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, abs, Iif, nested iif, switch, immediate window, isnull, gain loss report, string concatenation, report formatting, subscriber gain loss, concatenate, IIF function, ABS function
Subscribe to Gain or Loss
Get notifications when this page is updated
Intro In this video, we will talk about how to display gain or loss values in your Microsoft Access reports using formulas with the If and Abs functions, and show how to return phrases like "gained three" or "lost two" instead of just positive or negative numbers. We will look at handling null values, using both nested If statements and the Switch function, and show you how to create a custom VBA function to simplify this process across your database.Transcript Today we're going to talk about gain or loss. I have the words gain and loss in double quotes. I'll explain why in just a minute. We're going to identify gains and losses in your Microsoft Access database using If and the Abs (absolute value) function, and maybe a little bit later on I'll show you some other tricks too. So stick around.
Today's question comes from Alaina in Garland, Texas, one of my Gold members. Alaina says: "I currently print a monthly report for each district office showing whether they've gained or lost subscribers. Is it possible to include phrases like gained three or lost two in my reports instead of just three or negative two? I believe this would make the reports easier to read and look more professional." And I agree, Alaina. We can certainly do this a number of different ways. I'm going to show you a couple of different ways to do it.
They're all going to involve a formula using either If or the Switch function, the Abs value function, a couple of different ways you can do this. So let's talk about some prerequisites first.
If you don't know how to make calculated fields, go watch this video either in a form or your report or a query. You should know what concatenation is - that's putting multiple strings together. Go watch my If function video. That's the Immeditate If - it's basically an If Then statement in a function. You should know how to use Abs value. If you haven't watched these videos, go watch them. They're on my website. They're on my YouTube channel. They're free. Go watch them and come on back.
Here I am in my TechHelp free template. This is a free database. You can download a copy off my website if you want to. But let's just go into the customer table here. This data can be in whatever table you want. Let's say you've got a value down here. We'll call it AMT for amount. It's a Number of type Long Integer. This is whatever value you want to show - gain six or lost four or whatever.
Save that. Nothing big there. Now let's go make a query. You could do this formula directly in a form or report. I recommend putting it in a query, especially if you're a beginner.
Let's go to query design. Let's bring in our customer table. In here, I just want to show the customer ID, the first name, and that amount field.
Save this as, I don't care, customer Q. Whatever you want to call it - gains or loss Q, whatever - doesn't matter.
Let's put some data in here so we have some records with some data. Put in some positive values, some negative values, maybe a couple of zeros. We'll leave a bunch of nulls in there too.
So I've got some pluses, I've got some minuses, I've got some zeros.
Now the goal here is to say "gained five," "gained seven," "lost four," that kind of stuff.
Let's start off with a calculated query field. We'll put that right here. I'm going to zoom in so you can see it better - Shift+F2. I'm going to call this GOL for "gained or lost."
It's going to be: If amount (that's my field, Amount) is greater than or equal to zero, then "gained" and then a space (don't forget your space). If it's less than zero, it'll say "lost" space. Close up the If function and then ampersand the Abs value of the amount, so it'll say "gained four" or "lost two," because Abs is going to turn a negative into a positive.
So, If Amount is greater than or equal to zero, say "gained" and then the value. Otherwise, it'll say "lost" and then the value. That's a basic If function and the Abs value.
Ready? Save it and run it, and there you go. Looks good.
Now, what about these null values down here? Values with nulls in them don't show anything. So what do you want to do? Well, we can treat that as zero if you want. There are a lot of different options. However you want to handle this stuff - these are your Legos. You put them together however you want. I'm going to say let's treat any null values as zero.
So let's just come in here and we'll just put NZ around each place where we use the Amount function. We'll say "NZ," that's Null-to-Zero, and then we'll put a comma zero there. That tells us what to make the value if it's null. If Amount is null, make the value zero. And then we'll put it there too.
Save it. I don't think I have extra or missing closing parentheses. Let's see. What did I do? NZ, I forgot one extra closing parenthesis right there. This is the parenthesis for NZ. Then we need one more for the Abs value, which is that one right there. I wish these were colored! I made a video where I showed you how to color them - not in the actual query builder; we did it in a text box.
Now run it. Now all these null values down here show "gained zero." Looks good. But you know what? "Gained zero" really doesn't sound right because you don't gain zero - I mean, technically you gain nothing. How about we put in there "no change" - they stayed the same, put the words "no change" in there.
Let's come back in here, design view. We need a third condition now, so that tells me I need to nest two Ifs together. So come over here, we're going to put a third If function.
We're going to say: If NZ(Amount,0) equals zero, then put "no change" in here. Otherwise, the whole second If function will then run because it's not zero, so that means it's positive or negative. And then again, don't forget another parenthesis.
So this is the outer If statement and it ends there, and this is the inner If statement right there.
Run it. There we go, we've got "no change" for the null values and the zero value.
If you want to learn more about nested If functions, I cover them in this combining names video. Go watch this.
Now, in addition to nesting Ifs - I find nested If functions to be notoriously difficult to read - I actually prefer using a Switch statement instead. If you have three or more possible outcomes, use Switch.
I've got a whole video on the Switch function as an alternative to nested Ifs. So we're just going to rewrite this whole thing.
We're going to say Switch. You do condition, value, condition, value, back and forth. There's no limit - well, there is a limit; it's like 25, so it's really long, but you're never going to hit it realistically.
So we're going to say if NZ(Amount,0)=0, then "no change." Next, if that's greater than 0, then "gained" space and our value. The third and final condition: if it's less than 0, then "lost" space and the value. Now, for "lost," we don't even really need to use Abs value anymore because we can just take this thing and multiply it by negative one because now we know it's negative. Just an alternative there. Close it up, and there's your Switch statement. Condition, value if true, condition, value if true, condition, value if true.
Run it, and there we go, same results. But I think it's just easier to read. Switch.
Now, looking at all this, we could even get rid of this NZ(Amount,0) thing. We could make it a separate field, and then maybe have a Value N, which would make this even easier to read. But you know what? We could actually take advantage of the fact that we do have some null values, because to me, null doesn't say zero. Null really says, "I have no data."
Null would mean that for these people here, we didn't get any data. So if it's your regional offices, you might want to get on the phone and say, "George, where's my report? I need to know how many subscribers you gained." So there's a big difference between null and zero. No data versus some data, and the data is zero.
So why don't we do this instead? Let's go back into here. Let's say for our condition: If IsNull(Amount), then "no data." Otherwise, once we get to this point, now we know Amount has a value, so we can actually get rid of the whole null thing, the whole NZ thing, you just put Amount. If Amount equals zero, or if Amount is greater than zero, or if Amount is less than zero. Now it gets even simpler to read - now, each of these other conditions doesn't have to handle null values.
See how it works? If IsNull(Amount), put "no data," you're done. Now, if you get past this, you know these values have something in them. If Amount equals zero, then "no change" (which is different from "no data"). Otherwise, if Amount is greater than zero, put "gained" whatever. Otherwise, if it's less than zero, put "lost" whatever.
Run it. There you go. Sweet. That is what I would use as my final function for this. It takes care of all those considerations. I'm just going to copy this and put it in the notes down below if you want to use it.
You could also make this your own function.
We've been doing beginner stuff so far, just working in queries, but let me stick a little developer action in here for you if you want to learn a little bit of developer stuff. This gets so much easier to work with if you make a function out of it. This way you don't have to have the function we just wrote everywhere in your database. If it changes, you only have to change it in one spot, not in this query and that report and this form.
If you've never done any VBA programming before, don't panic. I've got a whole 20-minute video that gets you through the basics, teaches you what it's all about. Go watch this. Once you get started, you'll see it's really easy. It's not that hard at all.
If you've done some VBA programming, go watch "Create a Function" where I teach you how to make your own function in VBA that returns a value based on whatever parameters you send to it. It's a little more advanced but still not that tough.
Then go watch this video on the If Then statement. We're going to use If Then Else. Yes, there is a Switch statement in VBA as well. I've got a separate video on that. Today I'm going to keep it simple, though. We're just going to use If Then.
Let's save this and close it. What we're going to do now is make our own function. I'm going to come down here and find my global module. If you're using my database, you'll have a global module. This is code that every form and report in the database can use. If you don't have that already, click on Create and then Module over here.
That will put you in the VBA editor, which looks like this. I have to move it because I was working with it earlier, and I like to make it nice and big on my screen.
Ignore this stuff over here. You might or might not have stuff in here. Don't worry about it. Just come down to the bottom.
We're going to make a Public Function. Public means everybody in the database can use it - every form, every report, other modules, and so on. Function means it's going to return a value as opposed to a Subroutine, which is just another type of procedure that doesn't return a value, it just does some stuff, like this one counts a number of seconds.
But Public Function - what do you want to call it? Let's call it GOL, gained or lost. Or whatever you want to call it, I don't care. Name it whatever you want. Let's call it GainOrLoss; it makes more sense.
Now, we're going to send into it a value, AMT, as what kind of value is it? Let's just say for now it's a Long Integer. You could make it a Currency value if you're dealing with money. You could make it an Integer, but I always prefer Long Integers; they're just big integers.
GainOrLoss is going to return a value. Let's have it return the string that we want, so we can just display that in our forms and reports. So it's going to return a string value as String - that will say "gained three," "no data," or whatever.
Let's take care of the three basic conditions first. We got zero, negative, and positive. We'll deal with null at the end of the video, because null is a little special, something extra.
We're going to say: If AMT = 0, then GainOrLoss = "no change." GainOrLoss (the name of the function) is the value that it returns. ElseIf AMT > 0, then GainOrLoss = "gained " & AMT. Else (this means it's less than zero), GainOrLoss = "lost " & AMT * -1. End If.
If you're wondering about the null value, we'll get to it. Let's make sure this works first.
Now, you can go over to your query and test this, or you can test it right here using something called the Immediate Window.
Go up to View and then Immediate Window. Down here, you can use this to query stuff up here. For example, watch this: Start off with a question mark, then type in GainOrLoss(1). Press Enter and look at that, it returns the value - it shows you what that function would normally return: "gained 1."
Come up here and let's change this to -9 and then press Enter. "lost 9." See that? Put a zero in here, Enter. "no change." See that?
Now, we can come over, save this, always throw in a Debug Compile, come back out, and go back to our query again where we made our Customer Q in design view. Let's change this now, this GOL to GainOrLoss(AMT).
Hit OK, save it, run it, and there we go. Looks good. These are working, except now we're getting errors down here for the null values. Why is that? Well, our function is expecting a Long. That means it can't handle null values.
There are two things you can do. The first and easiest thing you can do is go back to converting null values to something else, like zero. If you put NZ in here, save it, run it, now you go back to sending it zero. But that doesn't help us, because we want to see "no data."
So, in order to do that, in order to allow the function to receive a null value, you have to change this to a Variant. Variant means "I don't know what type of data to expect," it could be anything. It could be a string, it could be currency, it could be a date, it could be a number, it could be a null value.
You have to be careful in your code to take that into consideration. What I'm going to do now is, right up top here, I'm going to say: If IsNull(AMT), then GainOrLoss = "no data."
ElseIf AMT = 0, and so on, we'll continue down. Now we can handle a null value.
Save it, Debug Compile, come back out here, run our query again, and there you go. Now we can handle the null value.
As you can see, if you've never done any VBA programming before, it's not that hard. It's really easy. If you intend on working with Microsoft Access for any period of time, learn a little bit of VBA. You don't have to learn a ton. Look what I just showed you today: in what, 10 minutes, we did something really cool. We wrote our own VBA function.
On my website, I've got tons of additional developer lessons. I start off with Developer 1. I take it nice and easy with the training wheels on, just like we did today. I go through all the introductions, a little bit more about statements and procedures, all that stuff.
Then we go up to the levels. As of right now, it's December 2023, I've got 45 different levels of developer training. I've got plenty of stuff to keep you busy if you want to learn how to build applications in Microsoft Access.
There you go, Alaina. There is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main problem Alaina wants to solve in her Access reports?
A. She wants to display subscriber changes as readable phrases rather than just numbers
B. She wants to display only positive subscriber changes
C. She wants to hide all subscriber change data from the reports
D. She wants to sort subscribers by last names
Q2. Which Access function is most recommended for converting negative numbers to positive numbers in this context?
A. Round()
B. Abs()
C. Len()
D. DSum()
Q3. Why is concatenation important in the formulas discussed in the video?
A. It allows combining text and numbers into a readable string like "gained 5"
B. It sorts the values alphabetically
C. It filters out negative numbers
D. It calculates totals
Q4. When creating a calculated field to display "gained" or "lost," which combination of functions is initially used?
A. If and IsNull
B. If and Abs
C. DLookup and Abs
D. Switch and DSum
Q5. What is the purpose of using the NZ() function in Access formulas?
A. To convert currency values into numbers
B. To handle null values by replacing them with zero or another specified value
C. To sort numbers in ascending order
D. To create a message box
Q6. In the improved formula, why was a nested If used?
A. To allow for three outcomes: "gained," "lost," and "no change"
B. To only handle two outcomes efficiently
C. To calculate totals for all fields
D. To filter out all null records
Q7. What advantage does the Switch function have over nested If statements for these types of reports?
A. It can only check for two conditions
B. It is more readable and easier to maintain with multiple possible outcomes
C. It sorts records automatically
D. It highlights null values in red
Q8. Why might you want to distinguish between a null value and a zero in the report?
A. A null value indicates no data was received, while zero means no net change occurred
B. Both null and zero mean the same thing
C. Null values should always be treated as zero for accuracy
D. Zero indicates a negative change
Q9. What method is suggested for handling "no data" (null) in the final version of the formula?
A. Treat null as zero and show "gained zero"
B. Use IsNull to check for null and display "no data"
C. Skip the record entirely
D. Convert all nulls to negative numbers
Q10. What is a key benefit of moving complex logic into a VBA function instead of writing the formula everywhere in the database?
A. Function code is more secure and encrypted
B. If the logic changes, you only need to update it in one place
C. Functions run automatically every time Access opens
D. It removes the need for queries
Q11. When declaring a VBA function intended to handle possible null values, what data type should you use for the parameter?
A. String
B. Long Integer
C. Variant
D. Currency
Q12. Which of the following would be the correct VBA code to handle all three outcomes and null values?
A. If AMT > 0 Then GainOrLoss = "gained " & AMT
B. If IsNull(AMT) Then GainOrLoss = "no data" ElseIf AMT = 0 Then GainOrLoss = "no change" ElseIf AMT > 0 Then GainOrLoss = "gained " & AMT Else GainOrLoss = "lost " & AMT * -1 End If
C. If AMT = 0 Then GainOrLoss = "no change" End If
D. GainOrLoss = AMT
Q13. What tool in the VBA editor allows you to quickly test function output without running the full application?
A. Output Table
B. Immediate Window
C. Debug.Print Command
D. Command Button
Q14. What does the developer recommend regarding VBA for Access users?
A. Avoid VBA because it is too complicated
B. Learn the basics of VBA because it can make many tasks easier and more flexible
C. Use only macros instead of VBA
D. VBA is not supported in Access
Q15. Why is it beneficial to use a global module for functions like GainOrLoss?
A. Only one user can use the function
B. It allows the function to be accessible to all forms, reports, and queries in the database
C. It increases the security of the database
D. It limits the function to only one table
Answers: 1-A; 2-B; 3-A; 4-B; 5-B; 6-A; 7-B; 8-A; 9-B; 10-B; 11-C; 12-B; 13-B; 14-B; 15-B
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 identifying gains and losses in your Microsoft Access database using the If function and the Abs (absolute value) function. I will also share some additional tricks as we explore this useful reporting technique.
The main purpose of this lesson is to help present the changes in values, such as subscriber counts, in a more readable way. Instead of simply displaying raw numbers like "3" or "-2", we want to show phrases like "gained three" or "lost two" so reports look professional and are easier to interpret.
Before getting into the specifics, let me review a few concepts you should be familiar with. Calculated fields are essential – you should know how to create them, whether in queries, forms, or reports. It's helpful to understand string concatenation, which is just joining different bits of text together. We will rely on the If function, which is called Immediate If in Access. This works much like an If-Then statement but as a function that returns a value. Knowledge of the Abs function is also necessary since we'll need to convert any negative numbers to their positive counterparts for display purposes. If you are unclear on any of these, I offer free tutorials on my website and YouTube channel and recommend reviewing those first.
Let's start by looking at the data. Suppose we have a table of customers with a field called AMT, set as a Long Integer, which represents the value we want to show – for example, the number of subscribers gained or lost. This could easily be in any table relevant to your own database.
To build the report, I recommend using a query. Bring in your table, and select the fields you need such as customer ID, first name, and the amount. Create some records that include positive values, negative values, and a few zeros, as well as some nulls.
The goal is for your query to display results like "gained five," "lost four," or "no change." For the calculated field, we use a formula that checks whether the amount is greater than or equal to zero. If it is, it shows "gained" and the amount. If it's less than zero, it shows "lost" and the positive version of the amount, using Abs.
When you run this query, it works well for numbers, but you might notice that records with null values show nothing. Depending on what you want, you can choose to treat nulls as zero – in which case, you can wrap the Amount with the NZ function. This function replaces nulls with zero. Make sure to pay attention to parentheses here to avoid errors.
Now, when you run the query, nulls display as "gained zero." However, that phrase does not sound natural. Instead, let's replace it with "no change." This requires a third condition, so nest an If function inside another If.
Here is how the logic flows: if the value is zero, display "no change." If not, nest another If that distinguishes between positive and negative numbers, displaying "gained" or "lost" accordingly. This way, both zero amounts and nulls show more relevant phrases.
Nested If statements are perfectly valid, but as the conditions grow, they become harder to read. For cases with three or more conditions, I recommend using the Switch function. Switch allows you to specify condition-value pairs, simplifying the logic when you have multiple possible results. With Switch, you can add cases for zero, positive, and negative values, and you might find it easier to maintain than nested Ifs.
There is also an important distinction to consider: a null value actually means that no data was submitted for that record, not just that the change was zero. For example, if you expect data from regional offices and get a null, you may want to flag that as "no data" instead of "no change." Handling this properly makes your reports more meaningful.
So, with Switch, you can first check for null amounts, displaying "no data," then handle zeros as "no change," and finally sort out positive and negative numbers appropriately.
Now, while this technique works well for queries, if you find yourself needing this logic in multiple locations throughout a database, it's better to create a custom VBA function. That way, you write the logic once and simply call the function wherever you need it. If any changes are ever necessary, you update just the function rather than every query, form, or report.
To create a custom function, open a module in the VBA editor. If you do not already have a global module, create one. Then, declare a Public Function, such as GainOrLoss, and have it accept an argument representing the amount. The function should return a string value that represents the result: "gained," "lost," "no change," or "no data" as appropriate.
To handle null values, declare the parameter as a Variant type. This allows the function to accept any input, including nulls, and process them correctly. Use If statements to check for null, zero, positive, and negative values and return the corresponding string.
After you have saved and compiled your code, you can use this new function in queries, forms, and reports, greatly streamlining your workflow and making any future adjustments simple.
Learning even a small amount of VBA can have a big impact on your Access development. I recommend working through the developer lessons on my website, which guide you from the basics all the way to advanced techniques.
So, to summarize, you have several options for displaying gains and losses in a readable manner in your Access database – ranging from nested If statements and Switch functions in queries, to custom VBA functions for even greater flexibility.
You can find a complete video tutorial with step-by-step instructions on all the techniques discussed here on my website at the link below. Live long and prosper, my friends.Topic List Identifying gains and losses using calculated fields in queries
Using the If function to display "gained" or "lost" text
Concatenating strings in query expressions
Applying the Abs function to show positive values for losses
Handling null values with the NZ function in queries
Using nested If functions for multiple display conditions
Replacing "gained zero" with "no change" in outputs
Implementing the Switch function as an alternative to nested Ifs
Displaying "no data" for null values using IsNull
Creating a custom VBA function to automate gain or loss text
Setting up function parameters to accept nulls using Variant
Testing custom VBA functions with the Immediate Window in VBA
Updating queries to use a custom VBA function for display |
