I have a Drug Report that is grouped by Drug Name then Prescription Fill Month. The goal of the report is to print the total number of pills dispensed for a certain drug by month and up to 6 months. The months are chosen by parameters.

 

There are two parameters that users can enter, Starting Date and Ending Date. The parameters are 4-character fields. A valid entry is 1109(November 2009). The user can enter a maximum span of 6 months. If more than 6 months is chosen the report will only show six months of data from the Starting Month Parameter.

 

I need to Record Select based on those parameters, but I'm not good with Crystal and Dates. The problem I'm having is telling crystal that if the Ending Month date is more than 6 months past the Starting Month date, then only include drugs that are within a 6 month rage from the Starting Month.

 

So it's a date comparrison issue. If Ending Date minus Starting Date is larger than 6 months then choose records up to six months past Starting Date, else choose records that fall between Starting Date and Ending Date.

 

Hope that makes sense.

 

Trust me, ANY help would be appreciated.

 

I have done something and it seems to give me the what I need. Let me know if there is an easier way.

 

I converted Starting Date parameter to a date that is the first day of that month (so 1109 becomes 11/1/2009) and Ending Date parameter to a date that is the last day of that month (1109 would be 11/30/2009). I did the first using a local strinvar and local datevar with the Starting Date. For Ending Date I used the same, but also dateserial(year(dDate), Month(dDate)+1, 1-1).

 

I then wrote a record selection formula that looks at the month name of the month 5 months previous to the ending date month and if that month name does not come before or is not equal to the month name of the Starting Date, I only allow those records whose Refill Date is greater than or equal to the Starting Date and whose Refill Date is less than or equal to the starting month plus 6 months minus 1 day (so if january is used it will give me records for january 1 through june 30), otherwise, if the Ending Date month is within 6 months of the Starting Date month then I return records whose Refill Date falls within the first day of the Starting Date month through the last day of the Ending Date month.

 

The code for the selection formula looks like this:

 

     then {@DrugDate}>={@PrintOption1Date}

 

Where Drug Date is refill Date, PrintOption1Date is Starting Date and PrintOption2Date is Ending Date.

 

Does anyone see any problems with this?

to keep your current process I think this will work:

 

Datefield in @PrintOption1Date to (if datediff('m',@PrintOption1Date,@PrintOption2Date)<7 then @PrintOption2Date else dateadd('d',-1,dateadd('M',7,@PrintOption1Date))