Future birthdays 1

[rprosser]

I need to produce a report that shows forthcoming birthdays, based on the sample Membership application. Given the Birthdate in the 'members' table, how do I find birthdays for (say) 20-30 days from today?

So far I have created a report (see below) which calculates the birthdays for this year and next year, using criteria to select the appropriate range. The report shows both derived birthday fields however, rather than just the relevant one as I require.

Any suggestions to 'fix' this report, or for better approaches, would be very welcome.

Thanks ...

Richard Prosser

------------------- Report ----------------

Field: Birthday1: DateValue(Day([Birthdate]) & "/" & Month([Birthdate]) & "/" & Year(Date()))

Criteria: Between Date() And Date()+30


Field: Birthday2: DateValue(Day([Birthdate]) & "/" & Month([Birthdate]) & "/" & Year(Date())+1)

Criteria: Between Date() And Date()+30

 

[CosmoKramer]

Since a person's birthday is the same every year, you should only need one criteria field in your query.

Try something like this:

Field:    Birthday: Format([Birthdate],"dd/mm")
Criteria: Between Format(Date(),"dd/mm") And Format(Date()+30, "dd/mm")

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[rprosser]

Thanks, but I need the two birthday fields to cater for the end-of-year situation, unless you know of a better way.

Anyway, I have found a solution now, by using IIf in a report: =IIf([Birthday1]>Date(),Format(DateDiff("yyyy",[Birthdate],[Birthday1])),Format(DateDiff("yyyy",[Birthdate],[Birthday2])))

 

[CosmoKramer]

I have no idea what you mean by "to cater for the end-of-year situation"...

And, how does your IIf statement help you determine who has upcoming birthdays??





A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[rprosser]

Well, it works, and I can't be bothered to explain it.

Thanks for your input.

 

[CosmoKramer]

That's not a healthy attitude to have in these fora. Information exchange and learning are benefits to participating here.

Sorry to bother you for such a simple request....



A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams