Crystal reports functions
I am trying to select only records in which their date falls from the current date to the end of the month three months later.
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 that it doesn't return any results. If I shoot the second part, I get both results, but I only want the dates for three months.
What am I doing wrong?
a source to share
From your example code, I am assuming that you are using Crystal Syntax to write your formula, so the assignment to the variable should be done using: "=" and not "=". "=" Used to compare values in Crystal Syntax. See here . (You may be mixing it with the basic syntax.)
So your code should read (unfortunately I don't have a 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
a source to share
Perhaps because you are adding 4 months - what's in the future - instead of subtracting them?
You are asking the question "end of month three months ago", but the timing of the selection (from 2009-05-14) is in the future, so it may be that I misinterpreted your question, or your question is misspelled.
Did you print out the ThreeMonthsAway value to see what it evaluates? Are you sure the 3-argument Date () form accepts arguments in year-month-day order (this is plausible, but I've also come across systems where the order is month-day-year)?
Some of the points raised here have been removed by correcting the question (or the comments have been relegated to the case as the question was corrected after the comments were made).
a source to share