Report birthdays for a given month

Moderators: Moderators, Tech Support

Post Reply
bagbyci408
Posts: 16
Joined: Sat Feb 07, 2009 1:14 pm

Report birthdays for a given month

Post by bagbyci408 »

I am using PCPlus 10.4 on WIndows XP. I am trying to find a way to report birthdays without considering the year. I have tried a number of SQL statements but I either get a null date returned or a type mismatch error. I did this years ago in MS Access by creating a SQL text string and executing it but there must be an easier way in VFP. I would appreciate any ideas.

Carl

Zorak
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Report birthdays for a given month

Post by Zorak »

You don't need a custom report for this.

The built in Selected Dates report found under Membership > Personal Profiles > Reports - Personal Profiles will do the trick.

Notice where you enter a range of dates MM/DD/YYYY, further to the right there are fields just for MM/DD selections.

bagbyci408
Posts: 16
Joined: Sat Feb 07, 2009 1:14 pm

Re: Report birthdays for a given month

Post by bagbyci408 »

Thanks,

This gets me out of the woods. I would still like to know the SQL syntax so that I could include birthday/month in a larger report if possible.

Carl

Zorak
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Report birthdays for a given month

Post by Zorak »

The selection would be:

Code: Select all

SELECT * FROM ME WHERE MONTH(BORN)=4
Change the 4 to whatever month number you need.

bagbyci408
Posts: 16
Joined: Sat Feb 07, 2009 1:14 pm

Re: Report birthdays for a given month

Post by bagbyci408 »

Thanks, I stated that very poorly. I had created a new date field that can be sorted and copied the birthdays to that field to include in reports. I was trying to use the update command to set the year to a fixed value like 1900 so that birthdays would sort in the proper order. The problem is that using the update command don't seem to work using syntax like :

UPDATE ME SET DATE5 = "{" + MONTH(BORN) & "/" + DAY(BORN) + "/" & YEAR(1"01-01-1900) + "}"

Some of the things that I have tried compiles okay but gives " / / " as the output. I suspect that it is a matter of using the right combination of quotation marks around the string components but it may require something more.

This is not critical now that I can use the built in report but it would be helpful.

Carl

Zorak
Tech Support
Tech Support
Posts: 3006
Joined: Thu May 13, 2004 9:59 am
Location: PowerChurch Software
Contact:

Re: Report birthdays for a given month

Post by Zorak »

Code: Select all

REPLACE ALL DATE5 WITH DATE(1900,MONTH(BORN),DAY(BORN)) FOR NOT EMPTY(BORN)

bagbyci408
Posts: 16
Joined: Sat Feb 07, 2009 1:14 pm

Re: Report birthdays for a given month

Post by bagbyci408 »

Marvelous, apparently I had the date components in the wrong order and had to handle the nulls. Thanks for your help.

Carl

Post Reply