Need help with Date range selection...

[stormtrooper]

Hello. I need to select dates in the range of the current date and a future date (28 days from the current date).

Here's what I have now:

NumberVar Cmonth:= month({datefield});
NumberVar Cdate:= day({datefield});
NumberVar Cyear:= year({datefield});

NumberVar TmpMonth:= month(currentdate);
NumberVar TmpDate:= day(currentdate);
NumberVar TmpYear:= year(currentdate);

If (Cmonth = TmpMonth) and (Cdate = TmpDate) then
TmpYear - Cyear

That returns every year with the current date (ie.today's date). If I use

NumberVar TmpMonth:= month(currentdate+28);
NumberVar TmpDate:= day(currentdate+28);
NumberVar TmpYear:= year(currentdate+28);

then I get every year with the future date (ie.4 weeks from today's date)

Now all I need is all the dates between them as well.

I'm new to Crystal, so I need some help. Easy in Access though.

Thanks, stormtrooper

 

[stormtrooper]

I think we might be straying away from the original question. It just seems that I was very close with this formula:

If Cmonth in TmpMonth to FtrMonth and CDate in TmpDate to FtrDate
Then TmpYear - Cyear

I get everything I want except I would get dates that are 2 days before and/or after the date range set. That is, the current date would be Apr.24, the current date + 28 would be May 22. I would have some dates that are Apr.22 and May 24.

The formula works for numbers up to and including 6 days. After that, it's no good.

 

[rhinok]

This is getting a little confusing; let's break it down.

From what I'm reading, you want to only return those records that meet two sets of criteria:

1) Age in [2,6,13]
2) Anniversary Date in CurrentDate to (CurrentDate +28)

Based on these criteria you need to define "Age" and "Anniversary Date"

You've defined "Age" as

{@FullYears}

numbervar CMonth:=Month({DOB.DOB});
numbervar CDate:= Day({DOB.DOB});
numbervar CYear:= Year({DOB.DOB});

numbervar TmpMonth:=Month(CurrentDate);
numbervar TmpDate:= Day(CurrentDate);
numbervar TmpYear:=Year(CurrentDate);

numbervar FtrMonth:=Month(CurrentDate +28);
numbervar FtrDate:=Day(CurrentDate +28);
numbervar FtrYear:=Year(CurrentDate +28);

If
CMonth in TmpMonth to FtrMonth and
CDate in TmpDate to FtrDate
Then
TmpYear - CYear

Your formula doesn't work because the Day value recycles at the start of the month. If you

ran this report today (04/24/2001) then your formula looks like this in plain English:

If
CMonth in 4 to 5 and
CDate in 24 to 21
Then
2001 - CYear

Crystal Reports interprets CDate range as 21 to 24; not what you are looking for...

The MS Access Table I created has only two fields: Name and DOB. I've only entered values

for the twelve sample Dates of Birth you've given. Because you haven't specified whether

the the 'Age' value is the Member's Age as of today or as of their Anniversary Birthdate, I

wrote two sample formulas:

{@CurrentAge}

//in full years:
NumberVar CYear;

If
Month({DOB.DOB}) < Month(CurrentDate)
Then
CYear:= Year(CurrentDate)
Else
If
Month({DOB.DOB}) = Month(CurrentDate) and
Day({DOB.DOB}) <= Day(CurrentDate)
Then
CYear:= Year(CurrentDate)
Else
CYear:= Year(CurrentDate)-1;

CYear - Year({DOB.DOB})

{@AgeonAnniversary}

//in full years:
NumberVar NextAnnivYr;

NextAnnivYr-Year({DOB.DOB})

This second formula shares the NextAnnivYr variable defined in the [COLOR=

blue]{@NextAnniversaryDate}[/color] formla which I've revised as follows:

{@NextAnniversaryDate}

NumberVar NextAnnivYr;
DateVar NextAnnivDt;

//Defines the Next Anniversary Year:
If
Month({DOB.DOB}) < Month(CurrentDate)
Then
NextAnnivYr:= Year(CurrentDate) + 1
Else
If
Month({DOB.DOB}) = Month(CurrentDate) and
Day({DOB.DOB}) < Day(CurrentDate)
Then
NextAnnivYr:= Year(CurrentDate) + 1
Else
NextAnnivYr:= Year(CurrentDate);

//Defines the Next Anniversary Date:
NextAnnivDt:= Date(NextAnnivYr, Month({DOB.DOB}), Day({DOB.DOB}));

//The Next Anniversary Date:
NextAnnivDt;

For data validation purposes, I wrote a {@DaystoAnniverary} formula:

{@DaystoAnniverary}

{@NextAnniversaryDate} - CurrentDate

The base records returned look like this (ordered by &quot;Days to Birthday&quot:

Name DOB Next Current Days to Age on Full
Birthday Age Birthday Birthday Years


Name8 04/24/199 04/24/2001 2 0 2 2
Name1 04/25/199 04/25/2001 1 1 2 0
Name12 04/29/199 04/29/2001 5 5 6 0
Name10 05/03/198 05/03/2001 12 9 13 0
Name2 05/12/199 05/12/2001 1 18 2 0
Name6 05/20/199 05/20/2001 5 26 6 0
Name9 05/25/198 05/25/2001 12 31 13 0
Name5 08/08/199 08/08/2001 4 106 5 0
Name3 12/12/200 12/12/2001 0 232 1 0
Name11 01/01/199 01/01/2002 5 252 6 0
Name4 01/13/199 01/13/2002 6 264 7 0
Name7 02/25/198 02/25/2002 12 307 13 0

My record selection criteria is:

{@CurrentAge} in [2,6,13] and
{@NextAnniveraryDate} in CurrentDate to (CurrentDate + 28)

Only one record is returned based on the twelve sample Dates-of-Birth you listed.

Name DOB Next Current Days to Age on Full
Birthday Age Birthday Birthday Years


Name8 04/24/199 04/24/2001 2 0 2 2


If change the record selection criteria to:

{@AgeonAnniversary} in [2,6,13] and
{@NextAnniveraryDate} in CurrentDate to (CurrentDate + 28)

then four records are returned:

Name DOB Next Current Days to Age on Full
Birthday Age Birthday Birthday Years


Name8 04/24/199 04/24/2001 2 0 2 2
Name1 04/25/199 04/25/2001 1 1 2 0
Name12 04/29/199 04/29/2001 5 5 6 0
Name10 05/03/198 05/03/2001 12 9 13 0
Name2 05/12/199 05/12/2001 1 18 2 0
Name6 05/20/199 05/20/2001 5 26 6 0

Please note, I only received six values where you listed seven expected returns above. This is because one of your Anniversary Dates is 31 days out...

 

[kenhamady]

I am sorry. I don't understand how doing your subtraction applies to selecting the records. Possibly someone else will and will help you trouble shoot it further if you explain it further.

As for me, if the last pair of formulas I posted don't do what you want, then I just am not getting it and will stop wasting your time.

Good Luck. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[stormtrooper]

Thanks rhinok, but I still get the message: 'A day number must be between one and the number of days in the month.' Do you have that leap year calculation?

 

[rhinok]

Actually, I was just reviewing my leap year calculations in a report I created and my formula specifically calculates the number of days in a month, accounting for leap year. That's not really applicable here. You can, however, determine a leap year using a formula similar to this:

If
Month({Orders.Order Date}) = 02 and
(Remainder(Year({Orders.Order Date}), 4) = 0 and Remainder(Year({Orders.Order Date}), 100) <> 0) or
(Remainder(Year({Orders.Order Date}), 400) = 0)
Then
29
Else
28

(This formula is part of Crystal Decisions Article c2000637)

>>I ran the {@Birthdate} formula and didn't get any records so they probably don't exsist.

Like Ken, I'm a little concerned about this statement. I'd try validating your data. The @Birthdate formula should return something. Comment out your record selection criteria and see what kind of records you get in @Birthdate. The formula substitutes a valid date in lieu of Feb 29. As there isn't any sophisticated code in the formula, you can pretty much rule out leap year as an issue. If you can rule out leap years then you may have other data issues in this field such as invalid dates, null values, low values, zeroes, etc... I would also create a Month formula and a Day formula (based on the date-of-birth field) and see what values are returned.

I think your on the right path with either Ken's or my formulas, but you need to validate your data...

 

[stormtrooper]

kenhamady, rhinok. Thanks for all your help. I sat down and figured it all out. Sorry to take up so much of your time. Here's what I came up with:

NumberVar Ccmonth:= month({V_CHILD_APPROVAL.PARTY_DOB});
NumberVar Ccdate:= day({V_CHILD_APPROVAL.PARTY_DOB});
NumberVar Ccyear:= year({V_CHILD_APPROVAL.PARTY_DOB});

NumberVar TmpMonth:= month(currentdate);
NumberVar TmpDate:= day(currentdate);
NumberVar TmpYear:= year(currentdate);

DateVar FourWkDate:= currentdate+28;
NumberVar FWMonth:= month(FourWkDate);
NumberVar FWDate:= day(FourWkDate);
NumberVar FWYear:= year(FourWkDate);

If Ccmonth = TmpMonth and Ccdate = TmpDate then
TmpYear - Ccyear
else
if month({V_CHILD_APPROVAL.PARTY_DOB}) = 2 and day({V_CHILD_APPROVAL.PARTY_DOB}) = 29
then if (date(year(currentdate),3,1)-1) in currentdate to (currentdate + 28) then
FWYear - Ccyear
else 0
else
if currentdate in date(year(currentdate),12,4) to date(year(currentdate),12,31)
then if (date(year(currentdate),month({V_CHILD_APPROVAL.PARTY_DOB}),day({V_CHILD_APPROVAL.PARTY_DOB})) in
currentdate to (currentdate + 28))
or
(date(year(currentdate)+1,month({V_CHILD_APPROVAL.PARTY_DOB}),day({V_CHILD_APPROVAL.PARTY_DOB})) in
currentdate to (currentdate + 28)) then
FWYear - Ccyear
else 0
else
if date(year(currentdate),month({V_CHILD_APPROVAL.PARTY_DOB}),day({V_CHILD_APPROVAL.PARTY_DOB})) in
currentdate to (currentdate + 28) then
FWYear - Ccyear
else 0