Control Source in Form Doesn't Display Query

[antmorano]

Hi- I just recently moved an expression that I had in only a Form to a
Query. The Expression runs great and I renamed the Expression to
"Retiree Age:..." On my Form- I go to change my Control Source to
"Retiree Age" and the age no longer comes up? I don't know why it
won't but any input would be greatly appreciated. I can't leave it in
the form b/c I need to generate a number of reports based on certain
ages.

I really appreciate this:

-Anthony Morano
Pension Fund Intern

 

[Amy Blankenship]

Hi- I just recently moved an expression that I had in only a Form to a
Query. The Expression runs great and I renamed the Expression to
"Retiree Age:..." On my Form- I go to change my Control Source to
"Retiree Age" and the age no longer comes up? I don't know why it
won't but any input would be greatly appreciated. I can't leave it in
the form b/c I need to generate a number of reports based on certain
ages.

If I were you I'd just delete the control that doesn't work. Then I'd show
the fields list and drag the Retiree age field to the form.

 

[Klatuu]

You do not want to store an age in a table. It will only be accurate for
less than 1 year. The correct technique is to store the birth date in the
table and create a Public function to calculate the age where ever you need
to display it. It can be on a form, in a report, or in a query.

Here is an age calculation function

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = year(DateToday) - year(Bdate) - 1
Else
Age = year(DateToday) - year(Bdate)
End If
End Function

for a form, you would call it from the Control Source
=Age(Me.txtBDay, Date())

for a report, it would be the same

for a query, you would create a calculated field:

Age: = Age([BIRTH_DATE], Date())

 

[Amy Blankenship]

You do not want to store an age in a table. It will only be accurate for
less than 1 year. The correct technique is to store the birth date in the
table and create a Public function to calculate the age where ever you
need
to display it. It can be on a form, in a report, or in a query.

Here is an age calculation function

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = year(DateToday) - year(Bdate) - 1
Else
Age = year(DateToday) - year(Bdate)
End If
End Function

for a form, you would call it from the Control Source
=Age(Me.txtBDay, Date())

for a report, it would be the same

for a query, you would create a calculated field:

Age: = Age([BIRTH_DATE], Date())

Why couldn't you simply use the DateDiff function?

 

['69 Camaro]

Hi, Amy.

for a query, you would create a calculated field:

Age: = Age([BIRTH_DATE], Date())

Why couldn't you simply use the DateDiff function?

Because DateDiff() is only guaranteed to work correctly on the last day of
the year. Any other day of the year, the age returned will be incorrect if
the birthdate is after today's date.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[John W. Vinson]

Age: = Age([BIRTH_DATE], Date())

Why couldn't you simply use the DateDiff function?

It counts *year changes*, not full years. DateDiff("yyyy", #12/31/2006#,
#1/1/2007#) is equal to 1 - but that would make a one-day old baby appear to
be a year old.

John W. Vinson [MVP]

 

['69 Camaro]

Hi, Klatuu.

For example, my birthday is today (it really is), so Had I done a datediff
prior to today, it would return 64 while I was still 63.

Happy birthday! Let me go get my fire extinguisher before we light the
candles, just in case you can't blow them out in time.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Klatuu]

Thanks, Gunny.
I blew out the candles, but it will be a while before the eyebrows grow back
in