Variables in Microsoft Access

Introduction

A variable is an area in memory that is used to hold a value for a program. Since the value can change regularly while the program is running, it is called a variable. In order to use this area in memory, you must ask the computer to reserve it for later use. This request is referred to as declaring a variable.

Since Microsoft Access doesn't inherently present a programming environment, in order to declare your own variable, you can use VBA, which is available with Microsoft Access. VBA requires you to do some programming. Even though it may not be the most 
complicated language in the world, it is still a programming language that you would have to learn. If you don't want to get into that area, in various cases you don't have to. You can cheat with Microsoft Access and use variables without declaring them

Practical Learning: Starting the Exercise

1. If you want to follow this exercise, start Microsoft Access and create a 
   database named Compound Interest
2. Create a table in Design View as follows:
    

   Field Name	Data Type	Additional Properties
   FrequencyID	AutoNumber	Primary Key
   Frequency

3. Save the table as Frequencies and switch it to Datasheet View
4. Enter the first frequency as Monthly, the second as Quarterly, the third as Semiannually, and the fourth as Annually
    
5. Close the Frequencies table
6. Create a new table in Design View as follows:
    

   Field Name	Data Type	Additional Properties
   CompoundID	AutoNumber	Primary Key Caption: Compound ID
   Principal	Currency
   Interest	Number	Field Size: Double Format: Percent Default Value: .0725
   Periods	Number	Field Size: Integer Default Value: 1
   FrequencyID	Number	Start the Lookup Wizard and select the Frequency column of the Frequencies table Caption: Frequency Default Value: 1

7. Save the table as CompoundInterest
8. Switch it to Datasheet View and close it

Creating the Application

One of the features of a variable is that the user neither sees nor uses it. In fact, this aspect, like all others in programming, are of little to no interest to the user. Based on this, you can cheat with your application by including one or many Windows controls in your form or report. To treat such controls as variables, you can simply hide them. Once they are hidden, you can use them as you see fit.

To hide a control in your form, you can include it in any section. All you have to do is set its Visible property to No.

Practical Learning: Designing the Form

1. Using the CompoundInterest table, design a form as follows:
    
2. Set the Name of the frame that contains the radio (option) buttons to fraFrequency
3. Save it as CompoundInterest
4. To create text boxes that would be used as variables, in the Toolbox, click the Text Box control and click in the left area of the Form Footer section
5. Change the caption of the label to Compound Type
6. Using the Properties window, change the following characteristics of the text box
   Name: txtCompoundType
   Record Source: =Switch([fraFrequency]=1,12,[fraFrequency]=2,4,[fraFrequency]=3,2,[fraFrequency]=4,1)
   Visible: No
7. Add another text box to the right of the previous one. Change its label's caption to i
8. In the Properties window, change the following characteristics:
   Name: txti
   Record Source: =CDbl(Nz([Interest]))/Nz([txtCompoundType])
   Visible: No
9. Add another text box to the right of the previous one. Change its label's caption to n 
10. Using the Properties window, change the following characteristics
    Name: txtn
    Record Source: =Nz([txtCompoundType])*CInt(Nz([Periods]))
    Visible: No
11. Expand the Detail section if necessary. Add a Rectangle box in the bottom area of the Detail section
12. Add a label to the top border of the rectangle to appear like a group box or frame. Change the label's caption to Results
13. Inside the rectangle, add a text box. Change its label's caption to Interest Earned
14. In the Properties window, change the following characteristics of the text box:
    Name: txtInterestEarned
    Record Source: =Nz([txtAmountEarned])-Nz([Principal])
    Format: Currency
    Decimal Places: 2
15. Add another text box inside the rectangle and to the right of the previous one. Change its label's caption to Amount Earned
16. In the Properties window, change the following characteristics of the text box:
    Name: txtAmountEarned
    Record Source: =Nz([Principal])*((1+CDbl([txti]))^CInt([txtn]))
    Format: Currency
    Decimal Places: 2
17. Reposition the controls so your form appears designed as follows:
     
18. Save and switch the form to Form View
19. Process a few loans to see the result
     
20. Close the form