Newbie: Problem with calculating age in macro

[S Clause]

Hello,

I receive a report every month upon which I run a macro that I've recorded
(below) in order to calculate whether a person is to receive a birthday card or
not. The macro uses the NOW function to calculate the person's age and if they
are 80+, they will get a card i.e if the person's birthday was Dec. 24, 1927,
then based on the formula "=(TEXT(NOW(),"yyyy")-1927)" the result would be 76.

It works perfectly except when I need to find out who is to get a card in
January, as I receive January's report in December. The formula that I am using
is based on the current year, and what I need is a formula that will calculate
the age using the current year +1, or if there is a better solution, please let
me know. I probably haven't explained this very well, but I'm hoping someone
might be able to make sense of what I am trying to do and point me in the right
direction.

Cheers,
Angela


' BCR_cleanup Macro
' Macro recorded 12/16/2003 by Angela Farmer
'

'
Sheets("Welcome").Select
ActiveWindow.SelectedSheets.Delete
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C2").Select
ActiveCell.FormulaR1C1 = "=(TEXT(NOW(),""yyy"")-RC[1])"
Range("C2").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "AGE"
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="Alumnus/Alumna (Grad)"
Selection.AutoFilter Field:=3, Criteria1:=">=80", Operator:=xlAnd
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

 

[fredg]

Hello,

I receive a report every month upon which I run a macro that I've recorded
(below) in order to calculate whether a person is to receive a birthday card or
not. The macro uses the NOW function to calculate the person's age and if they
are 80+, they will get a card i.e if the person's birthday was Dec. 24, 1927,
then based on the formula "=(TEXT(NOW(),"yyyy")-1927)" the result would be 76.

It works perfectly except when I need to find out who is to get a card in
January, as I receive January's report in December. The formula that I am using
is based on the current year, and what I need is a formula that will calculate
the age using the current year +1, or if there is a better solution, please let
me know. I probably haven't explained this very well, but I'm hoping someone
might be able to make sense of what I am trying to do and point me in the right
direction.

Cheers,
Angela


' BCR_cleanup Macro
' Macro recorded 12/16/2003 by Angela Farmer
'

'
Sheets("Welcome").Select
ActiveWindow.SelectedSheets.Delete
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C2").Select
ActiveCell.FormulaR1C1 = "=(TEXT(NOW(),""yyy"")-RC[1])"
Range("C2").Select
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "AGE"
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="Alumnus/Alumna (Grad)"
Selection.AutoFilter Field:=3, Criteria1:=">=80", Operator:=xlAnd
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Angela,
You have posted an Excel spreadsheet code to an Access database
newsgroup, so I'm unclear where you are using this. In any event, your
code to compute age is not going to work (as you have already found
out).
Below is John Vinson's code to return the correct age.

The expression should be all on one line!!
Watch out for an extra > if the line gets split when sent.
There should only be one > in the expression
(> Format(Date() etc,).

In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

Directly as the control source of an unbound control (in a Report or on
a Form):
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

You do know, I hope, that this Age computation should NOT be stored in
any table.
Just compute it and display it on a form or report, as needed.

 

[Greg Kraushaar]

Look up the DateDiff function in the Help file. I think you will find
ith will do all you want.