Hiding zero fields

[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

 

[Allen Browne]

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])

 

[Joe D]

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?

 

[Allen Browne]

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])

 

[Joe D]

Can you give me an example of what the IIF statment would look like?

 

[Allen Browne]

See the example in the previous reply.

Can you give me an example of what the IIF statment would look like?