cancel
Showing results for 
Search instead for 
Did you mean: 

Need help with date array

Former Member
0 Kudos

I have three date fields. They can range from all being null to all being populated with date of birth values.

I want to create three formulas: youngest DOB, second youngest DOB, and third youngest DOB.

I'd appreciate any help coding these.

Requirements: if the result for any of these formulas will be empty, I need to know what "value" will be in it, so I can test for this elsewhere in my report.

Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

I like having this orderly array of dates.

I need to extract dates from the array to use (multiple times) in formulas in different parts of the report. Is this possible?

Former Member
0 Kudos

To answer both your posts:

1. When you have only one date in your DB with the other two being null (for example, 05/30/1989 with the other two being null), you will only see 05/30/1989 in the array because, for this formula only, we chose the option of 'Default Values for Nulls'. So the two null dates become Date(0,0,0), which for the purposes of evaluation will be less than 05/30/2009. Since Maximum date value becomes the youngest you see 05/30/1989 and the other two values being date(0,0,0) will show up as blanks.

2. Yes you can extract the dates from the array to use in formulae. Add this to the end of the formula:


datevar Y1 := date(y[1]); // the youngest
datevar Y2 := date(y[2]); // middle child
datevar Y3 := date(y[3]); // the oldest

In your new formulae, code the following to use the three different dates:


whileprintingrecords;

datevar Y1; // the youngest
datevar Y2; // middle child
datevar Y3; // the oldest

Former Member
0 Kudos

That works. I also found a way to make a custom function called Extract Date that lets me select either the first, 2nd, or third element in the array.

One more question. These dates are DOB's for children. If I want to know the number of children, how would I do this?

Thanks.

Former Member
0 Kudos

You are really making me work on this one aren't ya?

Since we change null values to default, no easy way but do something like this:


whileprintingrecords;

datevar Y1; // the youngest
datevar Y2; // middle child
datevar Y3; // the oldest

numbervar total := 0;

if y1 <> date(0,0,0) then total := total +1;
if y2 <> date(0,0,0) then total := total +1;
if y3 <> date(0,0,0) then total := total +1;

total;

In the code above, it might be missing the not equal to symbol (greater than and less than symbols).

Edited by: Sanjay Kodidine on Jun 15, 2009 9:55 AM

Former Member
0 Kudos

I have really appreciated this help. You have helped me put together a fantastic report that's got my supervisor's head spinning!!! (that's a good thing).

Just another follow-up, if you have any patience left!

I turned the code above into a function, and it worked. I passed the three dates of birth and the requested element of the resulting array of dates, and the function returned a date, no problem (e.g., 1st element = youngest, etc.)!

Then, I tried to expand the function, but it won't work.

I am now trying to also pass the ages of the three children (numbervars) into the function, so I can exclude any dates of birth for children over age 18. I try to put the resulting dates of birth in an array, and apply the above logic to that new array. It won't work and keeps returning a null value.

Here's the coding. Any help would be appreciated.

***********

Function (NumberVar element, DateTimeVar ChildDOB1, DateTimeVar ChildDOB2, DateTimeVar ChildDOB3, Numbervar ChildAge1, Numbervar ChildAge2, Numbervar ChildAge3)

// Declare a new array of 3 elements of type datetime

datetimevar array DOB;

redim DOB[3];

if ChildAge1 >= 18 then DOB[1] = datetime(0,0,0) else DOB[1] = ChildDOB1;

if ChildAge2 >= 18 then DOB[2] = datetime(0,0,0) else DOB[2] = ChildDOB2;

if ChildAge3 >= 18 then DOB[3] = datetime(0,0,0) else DOB[3] = ChildDOB3;

// Declare a new array of 3 elements of type datetime

datetimevar array OrderedDOB;

redim OrderedDOB[3];

// Maximum date becomes the youngest in the new array

// Minimum date becomes the oldest in the new array

OrderedDOB[3] := minimum(DOB);

OrderedDOB[1] := maximum(DOB);

// Populate the middle element

if DOB[1] in OrderedDOB then datetime(0,0,0) else OrderedDOB[2] := DOB[1];

if DOB[2] in OrderedDOB then datetime(0,0,0) else OrderedDOB[2] := DOB[2];

if DOB[3] in OrderedDOB then datetime(0,0,0) else OrderedDOB[2] := DOB[3];

// Determine if there are duplicate dates

// Twins or Triplets

if OrderedDOB[2] = datetime(0,0,0) then

if DOB[1] = DOB[2] or DOB[1] = DOB[3] then OrderedDOB[2] := DOB[1] else

if DOB[2] = DOB[1] or DOB[2] = DOB[3] then OrderedDOB[2] := DOB[2] else

if DOB[3] = DOB[1] or DOB[3] = DOB[2] then OrderedDOB[2] := DOB[3];

date(OrderedDOB[element]); // The requested element

Former Member
0 Kudos

tsk, tsk...


if ChildAge1 >= 18 then DOB[1] = datetime(0,0,0) else DOB[1] = ChildDOB1;
if ChildAge2 >= 18 then DOB[2] = datetime(0,0,0) else DOB[2] = ChildDOB2;
if ChildAge3 >= 18 then DOB[3] = datetime(0,0,0) else DOB[3] = ChildDOB3;

In the above code, instead of ASSIGNING values by using :=, you are comparing by using just the equal sign.


if ChildAge1 >= 18 then DOB[1] := datetime(0,0,0) else DOB[1] := ChildDOB1;
if ChildAge2 >= 18 then DOB[2] := datetime(0,0,0) else DOB[2] := ChildDOB2;
if ChildAge3 >= 18 then DOB[3] := datetime(0,0,0) else DOB[3] := ChildDOB3;

Edited by: Sanjay Kodidine on Jun 16, 2009 1:15 PM

Former Member
0 Kudos

Sanjay,

You are truly a gentleman and a scholar (of Crystal Reports Design)!

Thanks again for all your valuable assistance.

Gary

Answers (1)

Answers (1)

Former Member
0 Kudos

Oh, and how will this work if two or three have the same date (e.g., twins, triplets?)

Former Member
0 Kudos

Create a formula with the following:


// Sample Data
datetimevar array x := [date(2009,01,01), date(1989,05,30), date(1999,12,31)];

// Declare a new array of 3 elements of type datetime
datetimevar array y;
redim y[3];

// Maximum date becomes the youngest in the new array
// Minimum date becomes the oldest   in the new array
y[3] := minimum(x);
y[1] := maximum(x);

// Populate the middle element
if x[1] in y then datetime(0,0,0) else y[2] := x[1];
if x[2] in y then datetime(0,0,0) else y[2] := x[2];
if x[3] in y then datetime(0,0,0) else y[2] := x[3];

// Determine if there are duplicate dates
// Twins or Triplets
if y[2] = datetime(0,0,0) then

if x[1] = x[2] or x[1] = x[3] then y[2] := x[1] else 
if x[2] = x[1] or x[2] = x[3] then y[2] := x[2] else
if x[3] = x[1] or x[3] = x[2] then y[2] := x[3];

// Display the the array
date(y[1]) & ' ' & date(y[2]) & ' ' & date(y[3]);

When you create the formula, in the fomula editor, choose 'Default Values for Nulls' so that it applies only to this formula.

You can declare 3 variables and assign each element. For example:

datetimevar Y1 := y[1]; // the youngest

datetimevar Y2 := y[2]; // middle child

datetimevar Y3 := y[3]; // the oldest

You can test for nulls by checking if each variable above equals date(0,0,0).

Edited by: Sanjay Kodidine on Jun 12, 2009 11:58 AM

Former Member
0 Kudos

I am still testing this, but here's something I can't figure out:

I tried it where there is only one date. The other two date fields are blank (i.e., not populated in my dbase table). The result is the one date. Why?

Wouldn't the first step assign the one date to both minimum and maximum?