Access 2010 automatically calculating whether child is under 2

  Neill 10:51 06 Feb 14
Locked

I have a simple database with a Yes/No field to state whether a child is under the age of two. Currently I just manually work out if, based on today, they are under 2 and tick the box. Is there a way that the box will be ticked/unticked automatically based on a calculation that today's date is more than two years after their birth date?

Or could the words "Under 2" or "Over 2" be inserted, whichever is easiest. Thanks

  Woolwell 11:55 06 Feb 14

I'm only on Access 2007 and the procedure is slightly different as I think that I would have to use a Query but Access 2010 allows you to have a calculated field in the table See this. You will then have to create an expression. I'll see if I can come up with a suitable expression.

  Woolwell 12:15 06 Feb 14

This expression IIf(Date()-[Table1]![Date of Birth]<730,"y","n") should work but doesn't allow for leap years. Suspect that there is a better solution.

  Woolwell 12:16 06 Feb 14

ps Date if birth is the field in which you have entered the Date of Birth and Table 1 is the required table. I pressed Post too soon.

  Neill 13:50 06 Feb 14

Am I right in thinking that this calculated field is the data type of a new field called Under2 in the same table (Children under 2 test with a dateofbirth field) as the child's names?

If so I get the the message "The Expression IIf(Date()-[Children under 2 test]![DateofBirth]<730,"y","n")" cannot be used in a calculated Column. Thanks

  Woolwell 15:20 06 Feb 14

I'm sorry my version of Access only permits calculated fields to be used as parts of queries and therefore I could not test it. Suggest that you create a query based on the table.

  Woolwell 15:22 06 Feb 14

Did you put something like Test: before the expression?

  Neill 16:13 06 Feb 14

No, just IIf(Date()-[Children under 2 test]![DateofBirth]<730,"y","n") I'll try a query but I need it to show up in a Table or Form. Thanks again

  Woolwell 16:30 06 Feb 14

Queries don't show in tables but the result of a query looks like a table. You can create a form using a query. I think that you may have missed a name and colon before the expression.

  Neill 17:51 06 Feb 14

Sorry I'm not helping here! Am I right to type Test: (or something) in the next available field in the Query window. Then IIf(Date()-[Children under 2 test]![DateofBirth]<730,"y","n")in the Criteria? Some progress in getting a Enter Parameter Value box asking me to type a criteria, which when I do (a date) I get I'm being too complex.

  Woolwell 18:36 06 Feb 14

The expression should read name: followed by the expression all on one line in in the field row. Criteria will decide whether it will be displayed or not.

Create calculated fields should help you to create a calculated field in the table without using a query.

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now