How to Show Major Birthdays in a Form or Report?

[VivDenham]

Hi

I am trying to create a Form in MS Access 2007 that will flag up Members 65th, 70th, 75th, 80th birthdays that are due to happen this year. The query works fine and flags up 2 members, 1 with a 65th birthday and 1 with an 80th birthday this year.

However, all the Birtdays appear on the form, i.e. for the lady with the 65th birthday, it also shows the date for her 70th, 75th and 80th birthdays.

I would like to be able to hide all the irrelevant dates and only show the 65th birthday (i.e. with a date of 2011) for the 1 lady, and only show the 80th birthday (i.e. with a date of 2011) for the 2nd lady.

I have tried doing code for "If [65th Birthday] is true, then Visible = true". Also tried "If Year(Now()) is true then...." but either both entries for the 65th Birthday disappear, or both stay showing.

I just want the one for the lady whose 65th birthday IS this year, to show.

Any ideas?

Thanks.
Viv

[TheSmileyCoder]

Its a bit hard to help when you provide little detail as to the solution you allready have in place. That said I would create a seperate function in a seperate module, lets call it: fBoolAnniversary (A function that returns a boolean, and is related to anniversary calculations

Public Function fBoolAnniversary(dtBirthday As Date) As Boolean
    'Tells whether this year is a anniversary year for the person
    'Anniversary Years are: 65,70,75,80,85,90.......
    Dim intCurYear As Integer 'Current Year
    intCurYear = Year(Date)
 
    Dim intBirthYear As Integer
    intBirthYear = Year(dtBirthday)
 
    Dim intAge As Integer
    intAge = intCurYear - intBirthYear
    'Notice that this tells us how old she will be this year,
    'not her actual current age
 
    Dim intMod As Integer
    If intAge - 65 < 0 Then
        'Person is not yet in the anniversary range
        fBoolAnniversary = False
        Exit Function
    End If
    If (intAge - 65) Mod 5 = 0 Then
        'Person's age is divisible by 5, so must be a anniversary year
        fBoolAnniversary = True
    Else
        fBoolAnniversary = False
    End If
End Function

Now you can use this in your select query, and simply filter on those records where the function returns true. It will return TRUE for all persons above 60 who' turns 65,70,75..... in the current year, I.e. all those anniversary's for 2011.

I also made a modified version will return True for all those who has a annivesary within 1 year from today.

Public Function fBoolComingAnniversary(dtBirthday As Date) As Boolean
    'Tells whether this person will have a anniversary within the next year
    'Anniversary Years are: 65,70,75,80,85,90.......
 
    'Calculate age that person will be in 1 year
        'Date 1 year from now
            Dim dtDate As Date
            dtDate = DateAdd("yyyy", 1, Date)
        'Difference in years
            Dim intAge As Integer
            intAge = DateDiff("yyyy", dtBirthday, dtDate)
 
    'Calculate if anniversary will happen
        Dim intMod As Integer
        If intAge - 65 < 0 Then
            'Person is not yet in the anniversary range
            fBoolComingAnniversary = False
            Exit Function
        End If
        If (intAge - 65) Mod 5 = 0 Then
            'Person's age is divisible by 5, so must be a anniversary year
            fBoolComingAnniversary = True
        Else
            fBoolComingAnniversary = False
        End If
End Function

[VivDenham]

Hi, thanks for such a speedy reply.

You say I have given too little info - well, here goes. I have a query where the Column has a Field name of 65th Birthday, and the calculation is:

Year(DateAdd("yyyy",65,[Member - Date of Birth]))

and the Criteria is : Year(Now())

This outputs a field and finds 1 lady who has her 65th birthday this year.

However, I also have columns in the query to find 70th, 75th etc birthdays this year.

So on the Form I now have Lady 1,with fields showing the year of her 65th birthday, 70th, 75th and 80th birthdays.

But in the VBA code behind this form, I want to be able to only show the fields that show the 2011 year for each person, whether tht 2011 appears in the 65th, 70th, 75th or 80th birthday column. With only 1 field showing per lady, for their birthday that occurs this year.

And I want to do this by making the fields either visible or not. I just cannot find a selection to put in the code which will leave the 65th birthday showing for the 1 lady, but make it invisible for the lady whose 80th birthday occurs this year.

Hope that gives you a bit more info. Thanks for your help.

Viv

[TheSmileyCoder]

Dear Viv
I have tried to read your post as well as the original, but still a bit unsure. Is this a continues form you are using?

If you could provide a screenshot of your form, I think it would speak volumes. Perhaps even use Paint to circle the areas you want changed.

EDIT: While your at it, a screenshow of your Query Design window for the form, could also be helpful.

[VivDenham]

Hi

Yes, my form is a continuous one.

However, I have managed to come up with a solution. In the query, I have created a new field called 65th Birthday This Year, and the calculation is

65th Birthday This Year: IIf([65th Birthday]=Year(Now()),[65th Birthday],0)

This outputs 2011 for the lady whose 65th birthday is this year, and a 0 for the lady whose 80th birthday is this year.

I have repeated for the 70th, 75th, 80th birthdays, and now my form shows the 2011 in the 65th birthday This Year column for the 1 lady, with zeros across the other columns, just as I wanted. This saves me having to scroll across the page looking for the birthday that is due to occur in 2011.

So............ problem solved. Many thanks for your help with this - as it turns out, the solution was simple, I just had not thought of it until you mentioned Continuous Forms, when it occurred to me that "obviously, making the field invisibie would occur on both ladie's lines".

Once again, thanks for your efforts.

Viv