Repeating field

[nvtjellis]

I have a DB that has immunization shot dates. There are 1 - 12 rows with 1 - 8 columns each. The rows are filled left to right with the name of the immuniztion and the columns value being a date the shot was given.
Not all row (immunizinations)entries or columns (dates shot given) are filled.
I need to count the number of fields in a column that have a date and post the last column's value and the number of that field.

POLIO HIP HEP B MMR VARICELL DTP
flda1 fldb1 fldc1 fldd1 flde1 fldf1
flda2 fldb2 fldc2 fldd2 flde2 fldf2
flda3 fldb3 fldc3 fldd3 flde3 fldf3
flda4 fldb4 fldc4 fldd4 flde4 fldf4
flda5 fldb5 fldc5 fldd5 flde5 fldf5
flda6 fldb6 fldc6 fldd6 flde6 fldf6
flda7 fldb7 fldc7 fldd7 flde7 fldf7
flda8 fldb8 fldc8 fldd8 flde8 fldf8

I don't know how to make access each row/column in an array.

 

[synapsevampire]

Makes no sense to me.

There are no dates in the example data,are you saying that it's referencing another tables field? If so, I fear that your dba needs a detox center.

Try posting technical information:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[nvtjellis]

I have a DB that has immunization shot dates.
There are 1 - 12 rows with 1 - 8 columns each.
The first row is the name of the immunization.
Under each column there is the date the shot was given.
The number of entries for a given shot can be up eight.
I want to count the number of dates in a give shot column, display the last shot date and the number of shot given including the shot date posted.
Below is a sample of the data.
The remaining date slots are blank, zero or null.

POLIO HIP HEP B VARICELL DTP 01/23/1994 01/23/1994 02/14/1998 01/23/1994 01/23/1994
06/06/1999 07/05/2000 02/14/1998 03/16/2000 04/14/1994 07/07/2000 05/25/1996
11/30/2001 06/06/1999

 

[lbass]

This looks like a spreadsheet where each column represents a separate date field, e.g., {table.poliodate}. If these are strings (another guess) and you only have nulls, not blanks or zeros, you could right click on each column field and insert a grand total (count). If you definitely have nulls as well as zeros and or blanks, then for each column, create a formula like the following:

if isnull({table.POLIOdate}) or
trim({table.POLIOdate}) = "" or
{table.POLIOdate} = "0" then
0 else
1

Then right click on this formula and insert a grand total (SUM, not count) to get the count for this column. Repeat for each type of shot. For the maximum, you should convert your original date field to a real date:

if isnull({cr_sampleshots_sheet1.POLIO}) or
trim({cr_sampleshots_sheet1.POLIO}) = "" or
{cr_sampleshots_sheet1.POLIO} = "0" then date(0,0,0) else
cdate({cr_sampleshots_sheet1.POLIO})

Then right click on the formula and insert a maximum. Inserting a maximum on a string date will not give you the correct data.

If this doesn't work for you, you need to specify exactly what fields are available for your report and their datatype.

-LB

 

[nvtjellis]

I'm not being very clear. These are fields 1-8 with dates in them, or null,zero or blank. I need to increment through
field 1-8 checking for the presence of a date with the format mm/dd/yyyy, ie 12/25/2003. Just how do you increment from one field to another? If it was one large field, I would be able to do it.

To move the dates to a temp field is wasted coding.

 

[synapsevampire]

I'd like to help, but as you say, you're not being very clear.

A simple means to convey requirements is to supply:

Crystal version
Database/connectivity
Example data (which you did)
Expected output (based on the example data)

You might use a formula with a series of IFs, such as:

If isdate({table.field8}) then
"Field 8:"&{table.field8}
else
If isdate({table.field7}) then
"Field 7:{table.field7}
else
...you get the idea...
else
"No dates available"

A wild stab as you don't share what the output should look like, rather you try to describe it with text.

You also might do this more readily using a SQL Expression and real SQL, but that's Crystal version and database dependent.

-k