printing only fields with data on a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to print all dates within a time period , and 24 different
fields if they have data. i would like the print out to look like this:

date: field One, field two, etc.

I would appreciate any suggestions.
 
Hi, NCCarol.

The strategy depends on what you're trying to do. Do you wish to:

1) Print only those records where at least one of the 24 fields in not
blank, with a comma placeholder for blank fields, e.g.,

1/4/2005: Field1Value,,,,Field5Value, ...etc.

or

2) Print only those records where ALL of the 24 fields are not blank and not
any others

or

3) Print only the non-null value fields for each record. This is possible
but you might lose the ability to know which field the value came from.

Date Input Strategy: Create a simple unbound form with two textboxes with a
Date input mask in which to input the beginning and end dates, named, say,
BegDate and EndDate.

General Query Strategy: Create a query that has the criteria row in the
Date column equal to:

Between Forms!YourFormName!BegDate And Form!YourFormName!EndDate

Approach-Specific Query Strategies:

1) In the first Criteria row, add the criteria Is Not Null to the first of
the 24 fields. In the next row, add Is Not Null to the second field, and cut
and paste the Between... criteria to the 2nd row of the Date field. Continue
until you have 24 rows.

2) This requires a logical AND. Place Is Not Null in the 1st criteria row
of all 24 fields.

3) Select all 24 fields.

Report strategies

1) & 2) A Calculated Control. Set the Control Source to:
= [Date] & ": " & [Field1] & ", " & [Field2] & ... [Field23] & ", " &
[Field24]

3) A Calculated Control. Set the Control Source to:
= [Date] & ": " & IIf(IsNull([Field1]),"",[Field1]&", " ... &
IIf(IsNull([Field24]),"", [Field24])

Hope that helps.
Sprinks
 
I need to show each record that falls within a date period, and only print to
the report those fields that are not null or greater than 0. The report is
based on each client:
If the date falls between start date and end date then , if field 1 is not
null and is greater than 0, print the value, if field 2...
I want to avoid having big spaces print when a field does not meet the
criteria.
I had thought I might be able to do something with a Select statement - but
am not sure how to set it up.

Hi, NCCarol.

The strategy depends on what you're trying to do. Do you wish to:

1) Print only those records where at least one of the 24 fields in not
blank, with a comma placeholder for blank fields, e.g.,

1/4/2005: Field1Value,,,,Field5Value, ...etc.

or

2) Print only those records where ALL of the 24 fields are not blank and not
any others

or

3) Print only the non-null value fields for each record. This is possible
but you might lose the ability to know which field the value came from.

Date Input Strategy: Create a simple unbound form with two textboxes with a
Date input mask in which to input the beginning and end dates, named, say,
BegDate and EndDate.

General Query Strategy: Create a query that has the criteria row in the
Date column equal to:

Between Forms!YourFormName!BegDate And Form!YourFormName!EndDate

Approach-Specific Query Strategies:

1) In the first Criteria row, add the criteria Is Not Null to the first of
the 24 fields. In the next row, add Is Not Null to the second field, and cut
and paste the Between... criteria to the 2nd row of the Date field. Continue
until you have 24 rows.

2) This requires a logical AND. Place Is Not Null in the 1st criteria row
of all 24 fields.

3) Select all 24 fields.

Report strategies

1) & 2) A Calculated Control. Set the Control Source to:
= [Date] & ": " & [Field1] & ", " & [Field2] & ... [Field23] & ", " &
[Field24]

3) A Calculated Control. Set the Control Source to:
= [Date] & ": " & IIf(IsNull([Field1]),"",[Field1]&", " ... &
IIf(IsNull([Field24]),"", [Field24])

Hope that helps.
Sprinks

NCCarol said:
I would like to print all dates within a time period , and 24 different
fields if they have data. i would like the print out to look like this:

date: field One, field two, etc.

I would appreciate any suggestions.
Click to expand...
Click to expand...
 
NCCarol,

What you're trying to do, then, is Case 3 as I described it. However, since
you don't want to print any records that have all null values in the 24
fields, you should create your query as I described it for Case 1, with 24
criteria rows.

Create your input form to allow the user to input the beginning and end
dates, with a command button to run your report based on your query.

Then create the calculated report control that concatenates non-null values
based on the IIf and IsNull functions.

Hope that helps.
Sprinks

NCCarol said:
I need to show each record that falls within a date period, and only print to
the report those fields that are not null or greater than 0. The report is
based on each client:
If the date falls between start date and end date then , if field 1 is not
null and is greater than 0, print the value, if field 2...
I want to avoid having big spaces print when a field does not meet the
criteria.
I had thought I might be able to do something with a Select statement - but
am not sure how to set it up.

Hi, NCCarol.

The strategy depends on what you're trying to do. Do you wish to:

1) Print only those records where at least one of the 24 fields in not
blank, with a comma placeholder for blank fields, e.g.,

1/4/2005: Field1Value,,,,Field5Value, ...etc.

or

2) Print only those records where ALL of the 24 fields are not blank and not
any others

or

3) Print only the non-null value fields for each record. This is possible
but you might lose the ability to know which field the value came from.

Date Input Strategy: Create a simple unbound form with two textboxes with a
Date input mask in which to input the beginning and end dates, named, say,
BegDate and EndDate.

General Query Strategy: Create a query that has the criteria row in the
Date column equal to:

Between Forms!YourFormName!BegDate And Form!YourFormName!EndDate

Approach-Specific Query Strategies:

1) In the first Criteria row, add the criteria Is Not Null to the first of
the 24 fields. In the next row, add Is Not Null to the second field, and cut
and paste the Between... criteria to the 2nd row of the Date field. Continue
until you have 24 rows.

2) This requires a logical AND. Place Is Not Null in the 1st criteria row
of all 24 fields.

3) Select all 24 fields.

Report strategies

1) & 2) A Calculated Control. Set the Control Source to:
= [Date] & ": " & [Field1] & ", " & [Field2] & ... [Field23] & ", " &
[Field24]

3) A Calculated Control. Set the Control Source to:
= [Date] & ": " & IIf(IsNull([Field1]),"",[Field1]&", " ... &
IIf(IsNull([Field24]),"", [Field24])

Hope that helps.
Sprinks

NCCarol said:
I would like to print all dates within a time period , and 24 different
fields if they have data. i would like the print out to look like this:

date: field One, field two, etc.

I would appreciate any suggestions.
Click to expand...
Click to expand...
Click to expand...
 
The isNull does not recognize a zero as null, so it still prints the zero's.
Otherwise it would work fine. without changing the zero's to nulls, - any
other thoughts?
I appreciate all the time you are taking to help me - thanks

Sprinks said:
Hi, NCCarol.

The strategy depends on what you're trying to do. Do you wish to:

1) Print only those records where at least one of the 24 fields in not
blank, with a comma placeholder for blank fields, e.g.,

1/4/2005: Field1Value,,,,Field5Value, ...etc.

or

2) Print only those records where ALL of the 24 fields are not blank and not
any others

or

3) Print only the non-null value fields for each record. This is possible
but you might lose the ability to know which field the value came from.

Date Input Strategy: Create a simple unbound form with two textboxes with a
Date input mask in which to input the beginning and end dates, named, say,
BegDate and EndDate.

General Query Strategy: Create a query that has the criteria row in the
Date column equal to:

Between Forms!YourFormName!BegDate And Form!YourFormName!EndDate

Approach-Specific Query Strategies:

1) In the first Criteria row, add the criteria Is Not Null to the first of
the 24 fields. In the next row, add Is Not Null to the second field, and cut
and paste the Between... criteria to the 2nd row of the Date field. Continue
until you have 24 rows.

2) This requires a logical AND. Place Is Not Null in the 1st criteria row
of all 24 fields.

3) Select all 24 fields.

Report strategies

1) & 2) A Calculated Control. Set the Control Source to:
= [Date] & ": " & [Field1] & ", " & [Field2] & ... [Field23] & ", " &
[Field24]

3) A Calculated Control. Set the Control Source to:
= [Date] & ": " & IIf(IsNull([Field1]),"",[Field1]&", " ... &
IIf(IsNull([Field24]),"", [Field24])

Hope that helps.
Sprinks

NCCarol said:
I would like to print all dates within a time period , and 24 different
fields if they have data. i would like the print out to look like this:

date: field One, field two, etc.

I would appreciate any suggestions.
Click to expand...
Click to expand...
 
Hi, NCCarol.

Use the Nz function (short for Null to Zero) to evaluate either null or zero
values as zero, and use it for your criteria in the IIf calls. For example,
instead of:

IIf(IsNull([Field1]),"",[Field1] & ", ")

use:

IIf(Nz([Field1])=0,"",[Field1] & ", ")

Hope that helps.

One last question: assuming NC stands for North Carolina, are you a Wake
Forest fan (where my daughter studies) or one of the evil Blue empire down
the road? <g>

Sprinks

NCCarol said:
The isNull does not recognize a zero as null, so it still prints the zero's.
Otherwise it would work fine. without changing the zero's to nulls, - any
other thoughts?
I appreciate all the time you are taking to help me - thanks

Sprinks said:
Hi, NCCarol.

The strategy depends on what you're trying to do. Do you wish to:

1) Print only those records where at least one of the 24 fields in not
blank, with a comma placeholder for blank fields, e.g.,

1/4/2005: Field1Value,,,,Field5Value, ...etc.

or

2) Print only those records where ALL of the 24 fields are not blank and not
any others

or

3) Print only the non-null value fields for each record. This is possible
but you might lose the ability to know which field the value came from.

Date Input Strategy: Create a simple unbound form with two textboxes with a
Date input mask in which to input the beginning and end dates, named, say,
BegDate and EndDate.

General Query Strategy: Create a query that has the criteria row in the
Date column equal to:

Between Forms!YourFormName!BegDate And Form!YourFormName!EndDate

Approach-Specific Query Strategies:

1) In the first Criteria row, add the criteria Is Not Null to the first of
the 24 fields. In the next row, add Is Not Null to the second field, and cut
and paste the Between... criteria to the 2nd row of the Date field. Continue
until you have 24 rows.

2) This requires a logical AND. Place Is Not Null in the 1st criteria row
of all 24 fields.

3) Select all 24 fields.

Report strategies

1) & 2) A Calculated Control. Set the Control Source to:
= [Date] & ": " & [Field1] & ", " & [Field2] & ... [Field23] & ", " &
[Field24]

3) A Calculated Control. Set the Control Source to:
= [Date] & ": " & IIf(IsNull([Field1]),"",[Field1]&", " ... &
IIf(IsNull([Field24]),"", [Field24])

Hope that helps.
Sprinks

NCCarol said:
I would like to print all dates within a time period , and 24 different
fields if they have data. i would like the print out to look like this:

date: field One, field two, etc.

I would appreciate any suggestions.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top