Showing a Calculated field from a Query in a separate Form

[TheElkster]

Hi

I like using Access 2007 but am a novice.

=============================================================
Scenario:

I want to show the age of each person in my database on a form

==============================================================

I have a table that holds a list of people and their date of birth [CadetT].

I have a corresponding form that shows the info in the above table [CadetF]

I created a Query [AgeQ] to calculate the age of each person's record using the following fields:

Field 1
Field: CadetID
Table: CadetT

Field 2
Field: Age: Int(DateDiff("d",[DOB],Now())/365.25)

I run the query and I get a list of all cadets and their current age.

I now wish to show the current age of each Cadet on their record on the form.

I added a text box and [after hunting on the net for some help] typed the following into the control source on a text box I added to the form to show each cadet's age:

=DLookUp("[Age]","AgeQ")

it sort of worked - but it seems to bring the age of the cadet in record 1 into all subsequent records. It's not looking at each record in the query in turn and pulling the correct info across.

I tried creating a relationship from the Age query to the cadet table but this didn't resolve things.

Any advice that doesn't involve jumping into VB is much appreciated.

Thanks

Paul

 

[TheElkster]

I got it to work!

Instead of having a separate query to work out the ages, I added a text box to the form and typed the following into the Control Source for that box:

=Int(DateDiff("d",[DOB],Now())/365.25)

now each record shows the correct age for that person.

Thanks

Paul

 

[TheAceMan1]

How are ya TheElkster . . .

More accurate would be:

[blue]=DateDiff("yyyy", [DOB], Now()) + Int(Format(Now(), "mmdd") < Format([DOB], "mmdd"))[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheElkster]

Hi Ace Man

I swapped my code for yours.... and whereas I got the correct age with the code I was using - your one gave me an age of -1 ???

 

[TheElkster]

Wait up.... I had a MS Security Alert box up - after I clicked "Enable this content" - it changed from -1 to the correct age!

Thanks again.

 

[TheAceMan1]

TheElkster . . .

To be sure ... a person does not age a year until that [blue]day[/blue] of that [blue]month[/blue] each [blue]year[/blue] ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]