Crystal Reports Date functions

Question

I'm trying to select only the records in which their date falls from the current date to the end of the month three months from now.

Currently there are two dates in the table that match the query:
Judy: 5/17/09
asdf: 8/9/09

This is my formula:

DateVar current = Date(DurrentDateTime); //takes the time off
DateVar ThreeMonthsAway = Date(year(CurrentDateTime), month(CurrentDateTime)+4, 1) - 1; // month+4, then -1 days to get last day three months away
{tblTenant.LeaseEnds} > current AND {tblTenant.LeaseEnds} < ThreeMonthsAway

The problem is, it returns no results. If I take off the second part of it, I get both results but I only want the dates within three months.

What am I doing wrong?

Answer 1

Perhaps because you are adding 4 months - which is into the future - instead of subtracting them?

You question asks for 'end of month three months ago', but the sample dates are (as of 2009-05-14) into the future, so it could be that I'm misinterpreting your question, or your question is mis-written.

Have you printed out the value of ThreeMonthsAway to see what it evaluates to? Are you sure the 3-argument form of Date() takes the arguments in the year-month-day order (it is plausible, but I've also encountered systems where the order is month-day-year)?

---

Some of the points made here have been addressed by fixing the question (or the comments have been rendered irrelevant because the question was fixed since the comments were made).

Answer 2

From your code example I guess that you are using Crystal Syntax for writing your formula, so the variable assignment must happen using ":=" and not "=". The "=" is used for comparing values in Crystal Syntax. See here for example. (Maybe you are mixing it with the Basic Syntax.)

So your code must read (unfortunately I don't have CR here to test it):

DateVar current := Date(CurrentDateTime); //takes the time off
DateVar ThreeMonthsAway := Date(year(CurrentDateTime), month(CurrentDateTime)+4, 1) - 1; // month+4, then -1 days to get last day three months away
{tblTenant.LeaseEnds} > current AND {tblTenant.LeaseEnds} < ThreeMonthsAway

Answer 3

You may find this technique useful: Crystal Reports: Named-Date-Range Parameters