Ask the experts at the SPAWAR Systems Center Charleston Technology Training Center Norfolk. The training team shares field experience, provides high-quality training and technical support.
Thanks to the following instructors for their input: Alice Butler, Donald Luckey, Dawn Adams, Steve Penn, Patty George, Kay Beasock, Pat Hutley, Tom Donnelly, Leasa Perkins, Stephanie Clark, Muriel Taylor, Jayne Soliz and Ronald Bailey.
Microsoft Access 2000
QUESTION: I am updating a command database and need to show the ages of children. How can I calculate the age of the children? The birthdays are entered into a date field called Child1DOB.
ANSWER: First, add a field to your table for the age of the child. If you are not using a form to input data into this table, then create one. If you are already using a form, you will have to add the age field just created. Change to Design View of the form, select the age field text box, right mouse click, and select the Properties value from the menu. Under the All tab, select the Control source row and a build button will appear on the right side of this row (looks like three dots...). Select this Build button to bring up the expression builder. In the expression builder, double-click on Functions from the first column and select Built-in functions (from the first column under Functions).
From the second column select Date/Time and from the third column select DateDiff. Next select the Paste button (above the third column). Your formula is pasted into the top window of the expression builder. Now you must change the values in the expression builder. On the first value of <> select this and replace with "m" to display months as the age. If you want years, enter "yyyy." Be sure to enclose the letters with the quotation marks. Next select <> and replace this value with the name of your birthday field (Child1DOB). Next select <> and replace it with the value of Now(). Be sure to use the "()" at the end of Now. This Now() expression is the current date. Delete all remaining parts of the expression that have not been used or changed, but leave the last ")" .
Your expression should now read exactly: =DateDiff("m",Child1DOB,Now()), where m equals months, child1DOB is the birthday of the first child field, and Now() is the current date. Be sure there are commas between each value. Now select the OK button on the upper right side of the expression builder window, select the Enter key on your keyboard and save your form. Change to Form View and review your entries in this field on the form. The child's age should be calculated into months or years depending on your choice.
QUESTION: In Access, how can I track the date records were last updated on a form?
ANSWER: In the table underlying the form, add a dtm (date-time) field to store the date last updated. If the form is based on a query rather than just one table make sure this field is added to the query. Add it to the form from the field list.