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
Report birthdays for a given month
Moderators: Moderators, Tech Support
-
- Tech Support
- Posts: 3015
- Joined: Thu May 13, 2004 9:59 am
- Location: PowerChurch Software
- Contact:
Re: Report birthdays for a given month
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.
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.
-
- Posts: 16
- Joined: Sat Feb 07, 2009 1:14 pm
Re: Report birthdays for a given month
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
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
-
- Tech Support
- Posts: 3015
- Joined: Thu May 13, 2004 9:59 am
- Location: PowerChurch Software
- Contact:
Re: Report birthdays for a given month
The selection would be:
Change the 4 to whatever month number you need.
Code: Select all
SELECT * FROM ME WHERE MONTH(BORN)=4
-
- Posts: 16
- Joined: Sat Feb 07, 2009 1:14 pm
Re: Report birthdays for a given month
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
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
-
- Tech Support
- Posts: 3015
- Joined: Thu May 13, 2004 9:59 am
- Location: PowerChurch Software
- Contact:
Re: Report birthdays for a given month
Code: Select all
REPLACE ALL DATE5 WITH DATE(1900,MONTH(BORN),DAY(BORN)) FOR NOT EMPTY(BORN)
-
- Posts: 16
- Joined: Sat Feb 07, 2009 1:14 pm
Re: Report birthdays for a given month
Marvelous, apparently I had the date components in the wrong order and had to handle the nulls. Thanks for your help.
Carl
Carl