Search code examples
crystal-reports

Record selection based on Next Sunday through end of 2 months out

I want to pull all records from the {@Calc Promise Date} field that have a date range of next Sunday through the end of next month. I have the end of next month part, and tried the next Sunday part using some similar examples on this site, but I am still off target on the next Sunday part.

Original try that would have a start of 5 days after today:

{@Calc Promise Date}>=DateSerial(Year(currentdate),Month(currentdate),Day(currentdate)+5)
and {@Calc Promise Date}<DateSerial(Year(currentdate),Month(currentdate)+2,1)

This was modified from help given to another user, but does not cover how to adjust the start date of the range to next Sunday.

I tried the following and have errors as I do not understand the correct layout, or if this will help in getting my desired start date.

{@Calc Promise Date} >= if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 1
then DateSerial(Year(currentdate),Month(currentdate),1+7)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 2
then DateSerial(Year(currentdate),Month(currentdate),1+6)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 3     
then DateSerial(Year(currentdate),Month(currentdate),1+5)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 4    
then DateSerial(Year(currentdate),Month(currentdate),1+4)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 5     
then DateSerial(Year(currentdate),Month(currentdate),1+3)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 6     
then DateSerial(Year(currentdate),Month(currentdate),1+2)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 7    
then DateSerial(Year(currentdate),Month(currentdate),1+1)
and {@Calc Promise Date}<DateSerial(Year(currentdate),Month(currentdate)+2,1)
Solution

  • You are almost done as per my knowledge writing this formula in Record Selection doesn't work instead create saperate formulas and write on liner in record selection.

    Create a formula Start Date:

    if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 1
then DateSerial(Year(currentdate),Month(currentdate),1+7)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 2
then DateSerial(Year(currentdate),Month(currentdate),1+6)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 3     
then DateSerial(Year(currentdate),Month(currentdate),1+5)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 4    
then DateSerial(Year(currentdate),Month(currentdate),1+4)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 5     
then DateSerial(Year(currentdate),Month(currentdate),1+3)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 6     
then DateSerial(Year(currentdate),Month(currentdate),1+2)
else if DayOfWeek(DateSerial(Year(currentdate),Month(currentdate),1),1) = 7    
then DateSerial(Year(currentdate),Month(currentdate),1+1)
and {@Calc Promise Date}<DateSerial(Year(currentdate),Month(currentdate)+2,1)

    Create a Formula End Date:

    DateSerial(Year(currentdate),Month(currentdate)+2,1)

    Now your Record Selection:

    {@Calc Promise Date}>={@Start Date}
and {@Calc Promise Date}<{@End Date}