Calculated Field - newbie question.

  • Thread starter Thread starter Steve Thomas
  • Start date Start date
S

Steve Thomas

I'm entering 'Year of Death' and 'Age at Death' into a form, and would like
the Date of Birth field in the main table to automatically calculate the dob
for each entry put into the form.
Could anyone explain how to achieve this please.
TIA
Steve.
 
Steve Thomas said:
I'm entering 'Year of Death' and 'Age at Death' into a form, and would like
the Date of Birth field in the main table to automatically calculate the dob
for each entry put into the form.
Could anyone explain how to achieve this please.
Click to expand...

Please rephase your question:

"the Date of Birth field in the main table to automatically calculate the
dob" doesn't need a calculation, or did I misunderstand?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
I'm entering 'Year of Death' and 'Age at Death' into a form, and would like
the Date of Birth field in the main table to automatically calculate the dob
for each entry put into the form.
Could anyone explain how to achieve this please.
Click to expand...

It sounds impossible. Someone dying on June 1, 2002, at age 100 could
have been born anytime between June 2, 1901 and June 1, 1902 - you
can't even specify the year, much less the exact date!

Storing the calculated field would seem to be unwise in any case - you
can calculated (as best as can be done) on the fly by using the date
functions: e.g.

DateOfBirth: DateSerial([Year of Death]-[Age At Death], 1, 1)

This will return January 1 of the guestimated year of birth.
 
Hi,
Sorry I didn't explain myself too well!
All I want is for the DOB field to automatically calculate the year, the
date is not important. Likewise within the nearest year or two is fine for
this project, as it ties in with census returns, which hardly ever give the
exact DOB anyway.
I enter the [Year of death] and the [Age at death], and simply want the DOB
field to complete itself for each entry.
Thanks again,
Steve.
 
I enter the [Year of death] and the [Age at death], and simply want the DOB
field to complete itself for each entry.
Click to expand...

Again... and I didn't express this very clearly:

If you can calculate a value from values which exist in the table
(well enough for your purposes) then it is best not to store the
calculated value in the table AT ALL. If the DOB is just an Integer
year field (which I'd recommend, since a Date/Time value gives a false
impression of precision), simply calculate [Year Of Birth] on the fly
in a Query based on your table by putting

[Year Of Birth]: [Year Of Death] - [Age At Death]

The Query can then be used as the recordsource for a form or a report,
or as the basis for a more advanced query. I see no benefit whatsoever
to store it in the table.

If you intentionally choose to violate relational principles and store
it anyway, you'll need to use the BeforeUpdate event of the Form to
"push" the expression into the field:

Me!txtYearOfBirth = Me!txtYearOfDeath - Me!txtAgeAtDeath
 
Aha!
Thanks, John.....I see what you mean...just having the ability to check the
DOB with a query is exactly what I wanted....I don't really need to 'store'
the info at all.
Great, at this rate, another 12 years, and I'll be an expert at Access....!
Seriously though, thank you ,
Regards,
Steve.
 
Steve Thomas said:
Aha!
Thanks, John.....I see what you mean...just having the ability to check the
DOB with a query is exactly what I wanted....I don't really need to 'store'
the info at all.
Great, at this rate, another 12 years, and I'll be an expert at Access....!
Seriously though, thank you ,
Regards,
Steve.
Click to expand...
in a query:
ApproxDOB: DateAdd("yyyy", [ageatDeath]*-1, DateofDeath)
 
Back
Top