Parsing the date field for date, month and year 3

[nishi1982]

Hello,
I'm doing a report where I have to take in the date field in mm/dd/yyyy format. While displaying the results, i have to sort them using the Month only, e.g. all JAN. results will be together irrespective of the date and the year associated with it same for all the months of the year. Can someone help me with this and let me know how to parse the date field.

 

[synapsevampire]

For sortation, create a formula of:

month({table.date})

For display purposes, just use the actual date field and right click it and select format field->Date and select out the appropriate format for display.

-k

 

[BruceBussell]

To get the parts of a Crystal Date use the Year, Month, Date Crystal functions

YearPart = Year({DateField})
MonthPart = Month({DateField})
DayPart = Day({DateField})

Then you can use these values for Groups or other formulas

 

[nishi1982]

Thanks BruceBussell and synapsevampire for the tips, however, I'm still ruggling to get it to work.

Synapsevampire,
You suggested that I should create a formula for sortation as month({DateField}), can you elaborate more on how to create it and where to put it like in Select Expert or somewhere else.

And BruceBussell,
I'm assuming that I need to write the formula in the Select Expert.
But when I write,

Month({DateField})

it gives out error saying Date is required before the Month.

If I write

MonthPart = Month({DateField})

it says "reaming text does not appear to be a part of the formula. Can you give me some more info. on how to use it.

Thanks both of you for your help.

 

[lbass]

If your date is a string field, then create a formula in the formula editor (field explorer->formula->new}:

left({table.stringdate},2)

You can then insert a group on this formula.

-LB

 

[BruceBussell]

lbass has it correct. You need to check and see if your date field in Crystal is a true date field or simply a mm/dd/yyy string field with a date name.

If it is a true date field then our Month formulas should work. If it is a string field like mm/dd/yyyy then lbass's latest reply should work.

 

[nishi1982]

Thank You all for your responses, but I need more help on this.
It may be that I wasn’t clear enough when I asked the question,

So let me state the problem again,
I'm creating a report, which shows the Birth Days of students in a chronological order in the year.
For example if two people have birth days as, 01/22/78 and 01/26/77, then my report should display 01/22/78 first and then the 01/26/77 and so forth.

The problem I'm facing is, if I sort by the Birth Day, since it is a date field it will sort by the year and not by the month.

If I use the Month function like,

Month({BirthDay})

I get an error saying Boolean Value is expected.

If I do

Month({BirthDay}) = 01 and 02 and 03 so on,

I get the results only for that particular month.

What I want to do is to see the records of the students in the order of their birthdays in the year. So ideally it should sort by month first then within those results sort by the date irrespective of their year of birth. Because we want one report where people can just go by the order in the report and send B'Day Celebration notifications whenever someone's birthday approaches.

So if there is any way to accomplish this in crystal please let me know,

And again Thank You all for your great help.

 

[lbass]

You still have not told us whether your field is a date or a string field. If it is a date field, then in the field explorer (NOT in the record selection formula area), create a formula like this:

date(year(currentdate), month({table.birthdate},day({table.birthdate})

Then go to report->sort records and add the formula there, so that it will sort in order. You can use this field in the detail section to display the birthday this year.

If your field is a string field, then create this formula:

date(year(currentdate),val(left({table.birthdate},2)),val(mid({table.birthdate},4,2})))

And use this as your sort field.

With either formula, if you want to display by month, insert a group on the formula and choose "print on change of month".

-LB

 

[nishi1982]

Thank You,
The Birth Day was a date field and when I put the above formulas in the Field Explorer it worked...
Again Thank You...