Hiding zero fields

  • Thread starter Thread starter Joe D
  • Start date Start date
J

Joe D

I'm using Access 2003. I have a report that reflects our account
receivables. The query is pulled from a large database with fields
that are populated by a DOS based system. I have a report that looks
like this:

PayDate1 PayAmt1 TransCode1
PayDate2 PayAmt2 TransCode2
PayDate3 PayAmt3 TransCode3

Many times the fields for 2 and 3 are filled with zeros. Is it possibe
to not have these fields display on my report and have them grow and
shrink so I can reduce the size of this report? Thanks
 
If you want to suppress all records where the PayAmt field is zero, create a
query. In the Criteria row of the query grid under PayAmt, enter:
<> 0
Use this query as the RecordSource for your report.

Alternatives:
You can suppress the display of a zero in a text box, by setting its Format
property to:
0.00;-0.00;\""

You can convert a zero to a null in your query, by entering this into the
Field row:
IIf([PayAmt]=0, Null, [PayAmt])
 
Thanks Allen

Changing the format property works for my pay amount field, but
doesn't seem to work for my pay date and bank fields. The data in the
pay date field looks like this: 0000/00/00 and the data in the bank
field is this: 000. Do I have to change what I put in the Format
property for these?
 
Use an IIf() expression in the query to replace "0000/00/00" with Null.
(Presumably this import field is text.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Thanks Allen

Changing the format property works for my pay amount field, but
doesn't seem to work for my pay date and bank fields. The data in the
pay date field looks like this: 0000/00/00 and the data in the bank
field is this: 000. Do I have to change what I put in the Format
property for these?

If you want to suppress all records where the PayAmt field is zero, create
a
query. In the Criteria row of the query grid under PayAmt, enter:
<> 0
Use this query as the RecordSource for your report.

Alternatives:
You can suppress the display of a zero in a text box, by setting its
Format
property to:
0.00;-0.00;\""

You can convert a zero to a null in your query, by entering this into the
Field row:
IIf([PayAmt]=0, Null, [PayAmt])
Click to expand...
Click to expand...
 
Back
Top