I work in a school and I'm trying to create a monthly student birthday report. I want the report be grouped by month. The database I am using records date of birth in one field (MM/DD/YY). The formula I am using is "If {demogrph.dm_dob}[1 to 2] = "01" then "January", "If {demogrph.dm_dob}[1 to 2] = "02" then "February", etc.. The formula editor returns that "A string or an array of values is required here" before the field name. I am a relatively new Crystal user. Any help is appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Birthday list- date formula problems 1
- Thread starter pjomara
- Start date
If your field is a date field. Place the field on your report and do a mouse over on the field. The type of field will show up on the "tool tip" that comes up.
E.g: {demogrph.dm_dob}(date).
If it is a date field, create this formula to get the months' names.
MonthName(month({demogrph.dm_dob})) Mike
Before: After
![[morning]](/data/assets/smilies/morning.gif "[morning] [morning]")
![[bugeyed]](/data/assets/smilies/bugeyed.gif "[bugeyed] [bugeyed]")
E.g: {demogrph.dm_dob}(date).
If it is a date field, create this formula to get the months' names.
MonthName(month({demogrph.dm_dob})) Mike
Before: After
-
1
- #4
chelseatech
Instructor
The problem with both the above techniques is that the months will print alphabetically.
If it is a date field, create a formula for their birthday next year with.
Date(2003,month({demogrph.dm_dob},day({demogrph.dm_dob}))
And then group/Monthly on this formula. Editor and Publisher of Crystal Clear
If it is a date field, create a formula for their birthday next year with.
Date(2003,month({demogrph.dm_dob},day({demogrph.dm_dob}))
And then group/Monthly on this formula. Editor and Publisher of Crystal Clear
synapsevampire
Programmer
It sounds like you have a date field.
You don't need to do anything fancy here, just group by the {demogrph.dm_dob} date, and you'll see that one of the options is to The Section Will Be Printed For Each Month.
This will build the month grouping.
To get January instead of 01/03, right click the group #1 Name field and select Format Field.
Select Customize->Date
Under Format you can change how each portion of a date is displayed, inclusive of omitting columns.
If you only want dates for this month and later, place something in the Report->Edit Selection Formula->record akin to:
{demogrph.dm_dob} >= date(year(currentdate),month(currentdate),1)
-k kai@informeddatadecisions.com
You don't need to do anything fancy here, just group by the {demogrph.dm_dob} date, and you'll see that one of the options is to The Section Will Be Printed For Each Month.
This will build the month grouping.
To get January instead of 01/03, right click the group #1 Name field and select Format Field.
Select Customize->Date
Under Format you can change how each portion of a date is displayed, inclusive of omitting columns.
If you only want dates for this month and later, place something in the Report->Edit Selection Formula->record akin to:
{demogrph.dm_dob} >= date(year(currentdate),month(currentdate),1)
-k kai@informeddatadecisions.com
chelseatech
Instructor
Synapse vampire usually has great suggestions, but he's missed one major fault with his suggestion.
Example: Three students with birthdays on 12-May-78,
18-May-80, and 10-Jun-78.
Grouping on the birthdate, by month will create groups of
May (78)
June (78) and
May (80)
Because grouping by Month also includes an automatic group by Year. The previous solution will only appear to work if all students have the same year of birth. So might appear to work for a while and then a stray from another year will mess the whole thing up.
Use my earlier solution, it will always work. Editor and Publisher of Crystal Clear
Example: Three students with birthdays on 12-May-78,
18-May-80, and 10-Jun-78.
Grouping on the birthdate, by month will create groups of
May (78)
June (78) and
May (80)
Because grouping by Month also includes an automatic group by Year. The previous solution will only appear to work if all students have the same year of birth. So might appear to work for a while and then a stray from another year will mess the whole thing up.
Use my earlier solution, it will always work. Editor and Publisher of Crystal Clear
synapsevampire
Programmer
Ahhh, good point, Chelsea, I was thinking of generating a list of birthdates, not birthdays each year, I should have paid closer attention.
Simplest would be to create a formula using month({demogrph.dm_dob}) to group by, then you'll get a list of all those B-Days in that month, the year seems unimportant if you're trying to just determine Birthdays.
For display, you can then use monthname or the totext() method as mentioned by mbarron or the automatated formatting in my post.
-k kai@informeddatadecisions.com
Simplest would be to create a formula using month({demogrph.dm_dob}) to group by, then you'll get a list of all those B-Days in that month, the year seems unimportant if you're trying to just determine Birthdays.
For display, you can then use monthname or the totext() method as mentioned by mbarron or the automatated formatting in my post.
-k kai@informeddatadecisions.com
- Thread starter
- #8
synapsevampire
Programmer
That's the long ways there, grouping by Monthy() is the same.
Then you can display a text formatted version by right clicking and formatting the field.
-k kai@informeddatadecisions.com
Then you can display a text formatted version by right clicking and formatting the field.
-k kai@informeddatadecisions.com
chelseatech
Instructor
If performance is an issue then SynapseVamire's Group by Month will work faster than the specified order.
Use an SQL expression for the month number and grouping on server to go even faster. Editor and Publisher of Crystal Clear
Use an SQL expression for the month number and grouping on server to go even faster. Editor and Publisher of Crystal Clear
How about grouping by the month formula, (returns 1 to 12)
then creating a formula for the group name to use the following:
monthname({@month})
That way you are grouping on the numbers in order but get the names you want for the months to appear on the report.
Christine
Crystal Training and Consulting
crosenbaum@Strafford.com
then creating a formula for the group name to use the following:
monthname({@month})
That way you are grouping on the numbers in order but get the names you want for the months to appear on the report.
Christine
Crystal Training and Consulting
crosenbaum@Strafford.com
- Status
Similar threads
- Question
- Locked
- Question
- Locked
- Question