- Begin by double-clicking on Create query by using wizard
- In the Available Fields
box, select the following fields by double-clicking on them
- Name
- DOB
- Click
- Change the title of the query to Birthday List
- Make sure that Open the query to view information is selected
- Click
The new query will appear with only two columns, Name and DOB. Switch to Design view then continue.
Now build the calculated field- click in the empty field name box to the immediate right of the field DOB
- instead of selecting an existing field, you will type the
calculation that you want Access to perform
- click on the Build
button to open the Expression
Builder (it is much easier to construct a formula using
the builder!)
- click on the Build
- Think about the formula needed to calculate Age:
- you need today's date
- you need the birthday
- you need the number of days in a year
- you need to state the age as a whole number
- and you need to understand date arithmetic
you may want to review the discussion about date functions and date arithmetic in the spreadsheet section
- first we need today's date
- double-click on Functions, then double-click on Built-In Functions in the left box
- in the center box, click on Date/Time
- in the right box, scroll until you see Now, double-click on it to enter that function into the expression
- the Now function will insert the current date into the expression
- next we subtract
- click on the minus or subtraction button
- next we add the student's birthday to the expression
- now double-click on Tables, then double-click on Field Names in the left box
- in the center box scroll until you see DOB, double-click on it to enter this field into the expression
- next we convert to years
- since dates are stored as serial numbers, the result of this subtraction will be the student's age in days
- to convert to years, divide by 365.25 (to account for leap
years!)
- click on the slash or division button
- enter 365.25
- add parentheses because of the orders of operation
- since the subtraction must be done first
- click to the left of the division symbol
- click on the right parentheses button
- click to the left of NOW
- click on the left parentheses button
- since the subtraction must be done first
 |
The calculation as described above will result in the age expressed in years with a fractional portion. In other words, one of the students may be 12.659852 years old. Obviously, one's age is not expressed that way! If we used the Properties of this field, we could set the field as a FIXED decimal with 0 decimal places (see picture to the right). However, that would not be correct because the student who is 12.659852 years old would have his/her age displayed as 13. For age, we need to drop the decimal portion of the number, not round the number. To do that, you can use the INT function which rounds a number down to the nearest integer. Thus, 12.659852 becomes 12. |
 |
| The complete calculation with the INT function would be INT((Now()-[Field names]![DOB])/365.25) | ||
- inserting the INT function
- click to the left of the left parenthesis of the NOW function
- type INT(
- now move the cursor to the immediate right of 365.25
- type )
- click
A Few Final Touches
- the expression now reads Expr1: INT((Now()-[Field names]![DOB])/365.25)
- change Expr1 to read Age
so that Age, not Expr1,
will appear in the query datasheet
- drag over Expr1 and retype Age
- right-mouse click and select Properties from the contextual pop-up menu, or you may instead click on the Properties button in the toolbar
Now try other calculated fields!