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

 

[kenhamady]

Why not:

{datefield} in CurrentDate to CurrrentDate+28? Ken Hamady

http://www.kenhamady.com/

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

 

[stormtrooper]

Still having trouble with this one kenhamaday. Where would I insert that so I don't get a date prompt?

 

[kenhamady]

This formula goes in the record selection formula (Report, edit selection formula, record)

You will only get a date prompt if there is a parameter field in use in the report. To get rid of the prompt, find all uses of the parameter field and remove them including:

Selection formula
all formula fields
all condition formulas

Also, delete the parameter field object from the design screen. Ken Hamady

http://www.kenhamady.com/

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

 

[stormtrooper]

Maybe the output could help on what I'm looking for:

Yrs of age: Date of birth:
2 4/23/99
2 4/23/99
2 4/23/99
2 4/23/99
2 4/23/99
2 4/23/99

6 4/23/95
6 4/23/95
6 4/23/95
6 4/23/95
6 4/23/95

13 4/23/88
13 4/23/88

Now, what I require is all the dates for the 2 yr olds from now to 4 wks from now and the same goes for the 6's and the 13 yr olds.

I've tried this:

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

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

That seems to work, but I'm getting dates like 4/20/99 etc. which is a date before the current date.

Please advise.
Thanks, stormtrooper

 

[kenhamady]

Is this a selection formula? I must be missing what you want to do. The formula I gave you will select (from the table) all records that have a date value that is between today and today + 28 days. Sounds like that isn't what you want to do.

First what fields and values do you have in the database? List for us a sample of 10 records, with raw values. And then list exactly what you want the final output to look like from these 10. Ken Hamady

http://www.kenhamady.com/

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

 

[stormtrooper]

The above formula is called '@FullYears' (for 'Yrs of age:').
'Date of birth' has a formula in the select expert called
'{@FullYears}in[13,6,2]'.

DOB
1999-04-25
1999-05-12
2000-12-12
1995-01-13
1996-08-08
1995-05-20
1989-02-25
1999-04-24
1988-05-25
1988-05-03
1996-01-01
1995-04-29

From the above, this is what I want:

Yrs of age: Date of birth:
2 4/25/99
2 5/12/99
2 4/24/99

6 5/20/95
6 4/29/95

13 5/25/88
13 5/3/88

 

[kenhamady]

So you want to only print records if the age is in your age list, and if the ANNIVERSARY of the birthddate is in the next 28 days. Not if the actual date is in the next 28 days?

Ken Hamady

http://www.kenhamady.com/

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

 

[stormtrooper]

yes

 

[kenhamady]

First you need a formula that returns the next anniversary date of the birthdate. It must be a date that occurs after today, so you can't just take the year of the current date. That doesn't take the year change into account. So use the following:

if month(CurrentDate) * 100+day(CurrentDate) > Month({Orders.Order Date}) * 100 + day ({Orders.Order Date})
then Date( Year (CurrentDate) + 1, Month({Orders.Order Date}), Day({Orders.Order Date}) )
else Date( Year (CurrentDate) , Month({Orders.Order Date}), Day({Orders.Order Date}) )

Now you can use the field in the selection formula as I showed you above:

{@field} in currentdate to currentdate+28 Ken Hamady

http://www.kenhamady.com/

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

 

[stormtrooper]

I get a message:
"A day number must be between 1 and the number of days in the month."

 

[kenhamady]

Looks like you ran into a leap year birth date, and I didn't account for that. Try adding the following line to the IF statement in the anniversary formula:

if ...

and Month({Orders.Order Date}) = 1

then...


I believe that the only January dates need to be adjusted. I tried to make it more generic, so that you could see the anniversary, but sine you are only looking forward 28 days you should be safe. Test it and see if it works. Ken Hamady

http://www.kenhamady.com/

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

 

[rhinok]

I built a sample report (based on a test table in MS Access) with the following formula:

{@NextAnniversaryDate}

NumberVar NextAnnivYr;
DateVar NextAnnivDt;

//Defines the Next Anniversary Year:
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;

The record selection criteria follows:

{@NextAnniveraryDate} in CurrentDate to (CurrentDate + 28)

Provided that you don't have a record of an individual born on February 29, this formula should work well. Presumably, you don't want to exlude those individuals born on February 29? If this is the case, you could create a {@Birthdate} formula and substitute it for the actual database Date-of-Birth field where necessary:

{@Birthdate}

//Determines the Member's Birthdate (March 1 substituted for Feb 29 babies):
DateVar BirthDate;

If
Month({DOB.DOB}) = 2 and
Day({DOB.DOB}) = 29
Then
BirthDate:= Date(Year({DOB.DOB}), 3,1)
Else
BirthDate:= {DOB.DOB};

BirthDate;

 

[stormtrooper]

kenhamady and rhinok. I still get the same message as above using both your methods.

 

[rhinok]

You may need to validate your date of birth data. Its possible that you have day value that doesn't exist for a month in one of your records. For example: 11/31/2000.

BTW, I thought about this after I entered the last message; My Birthdate formula substitutes March 1 for Feb 29 babies across the board. It doesn't retain Feb 29 during leap year anniversaries. I have a leap year calculation if this is important to you.

 

[stormtrooper]

Anything would help right now. Thanks

 

[kenhamady]

When you use Rhinok's approach, which formula causes the error? Ken Hamady

http://www.kenhamady.com/

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

 

[stormtrooper]

kenhamady, it is the {@NextAnniversaryDate} formula. I ran the {@Birthdate} formula and didn't get any records so they probably don't exsist.

 

[kenhamady]

The leap year is more manageable if you let CR's calendar do the work. So hear is a simpler approach using two formulas.

A) First a formula called @years:

if Month(CurrentDate) * 100 + day(CurrentDate) >
Month({@testdate}) * 100 + day ({@testdate})

then Year(CurrentDate) - Year({@testdate}) + 1
else Year(CurrentDate) - Year({@testdate})

B) Now use this to calc the anniversary date:

DateAdd ('yyyy', {@years} , {@testdate} )


C) Now you can use this anniv date in the selection formula. Ken Hamady

http://www.kenhamady.com/

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

 

[kenhamady]

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

What did this mean, exactly? Do you have blank DOB values in your data? Is the DOB field stored in the database as a date or datetime value? Ken Hamady

http://www.kenhamady.com/

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