Access Answers
Questions
- How can I determine an age in Access?
- Depending on whether you want it to round to the closest year or
round down to the last whole year, choose one of the following:
Rounded Age Field Name: (NOW()-[Birth date field name])/365.26 or
Rounded Down Age Field Name: INT((NOW()-[Birth date field name])/365.26)
{ In Access, using the backslash in place of the slash divides as well,
but returns an integer result.
So =(NOW()-[Birth date field name])\365.26 would give the same result
as using the INT function. }
Return to FAQ
-
| Name |
Birthdate |
Rounded Age |
Rounded Down Age |
| Smith Alexis |
9/29/1974 |
31 |
30 |
| Smith Amanda |
8/28/1965 |
40 |
39 |
| Smith Brenda |
4/27/1961 |
44 |
43 |
| Smith Carmen |
10/27/1943 |
61 |
61 |
| Smith Chris |
10/13/1967 |
37 |
37 |
| Smith Cliff |
2/20/1959 |
46 |
46 |
| Smith Corin |
11/5/1971 |
33 |
33 |
| Smith Dan |
12/23/1954 |
50 |
50 |
- How do you create an Access query to find everyone
born in a certain month, regardless of year?
- Use the MONTH() function to pull the month number from the birth
date field. Then you can use the number of the month to find everyone
born in that month regardless of year or day of the month. You
can also use the DAY() function to pull out the day so you can sort
them by day of the month. The following design grid would list everyone
born in November, sorted as above:
-
-
-
- This is a great query to set up as a parameter query. Entering
[Enter month:] on the criteria row under Birth Month would ask the user
to enter the month they wanted to list each time the query was run.
Return to FAQ
- I have an Access table that has the name all
in one field. Is there an easy way to divide it into Last Name
and First Name columns?
- Contrary to popular belief, yep, there is! Select and copy
the name column. Open Word and click Edit from the Menu
bar and then Paste Special. From the Paste Special dialog
box, choose Unformatted text.
If your original text was lastname, firstname (comma separating last
and first):
- Select all of the text (Ctrl+a works) and click Table
on the Menu bar. Point to Convert and choose Text
to Table. Under Separate text at: in the dialog box,
choose Comma.
- If your original text was lastname firstname (no puncuation between
names):
- Select all of the text and click Table on the Menu
bar. Point to Convert and choose Text to Table.
Under Separate text at: in the dialog box, choose
Other and type a space.
- If your list was simple first and last names (or last and first)
then all you have to do now is copy and paste it back into the original
document. (Add the appropriate fields.) If your list was
a little more complicated (some people had their last name entered as
two words instead of one) you will want to review and clean it up in
while you're still in Word.
Return to FAQ
Return to the top
