Hiding a Zero on a Report

[jrabenhorst]

Any help to this problem would be wonderful. I've got a list of data that gets sorted into two columns during a query (Filed and Unfiled). Then both columns are printed onto a report, and totaled at the end of the report. The major problem I'm having is in order to get a total in Access, a field must contain a number (0.00) to calculate it on the report. Because this data if sorted into two columns, when it doesn't exist in one column, it records a zero there in order to calculate correctly. Is there a way for Access to calculate these figures, but hide the zeros in both columns and only print other amounts?

John

 

[PHV]

It's simply a Format problem.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrabenhorst]

Where should I try to format the value? When I run the format in the report and I tell it to display zeros as "" then I get an error.

 

[PHV]

The Format property of the TextBox object has 4 sections.
You may try this one:
0.00;-0.00;;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrabenhorst]

Thanks for the help PHV but it still won't work. I'm using that code in the format field of the report, but the zeros are stilling showing up. I've tried other forumns and ideas, but to no avail. If I tell the computer to display "" if it is a zero then I get an error for my totals. Here is the SQL code for the query. Any more ideas?

SELECT PolicyInfo.ClaimNumber, PolicyInfo.PolicyNumber, PolicyInfo.RegisteNumber, BasicInfo.Filed, IIf([Filed]=Yes,[PolicyNumber],"") AS PolicyFiled, PolicyInfo.IssueDate, IIf([Filed]=Yes,[TotalAmount],"-") AS TotalAmountFiled, PolicyInfo.BeneficiaryL, PolicyInfo.BeneficiaryMI, BasicInfo.DateNotified, BasicInfo.DateofBirth, BasicInfo.DateofDeath, BasicInfo.LastName, BasicInfo.FirstName, BasicInfo.MiddleInitial, PolicyInfo.[DateFormsRcv'd], PolicyInfo.DatePaid, IIf([Filed]=Yes,[PlanCode],"") AS PlanCodeFiled, BasicInfo.Print, ([LastName] & ", " & [FirstName] & " " & [MiddleInitial]) AS Name, BasicInfo.CaseNumber, BasicInfo.InvoiceDate, IIf([Filed]=No,[PolicyNumber],"") AS PolicyNotFiled, IIf([Filed]=No,[PlanCode],"") AS PlanCodeNotFiled, IIf([Filed]=No,[TotalAmount],"-") AS TotalAmountNotFiled, Format([InvoiceDate],"mmmm") AS [Month], BasicInfo.Company, Format([InvoiceDate],"mm") AS InvoiceMonth
FROM BasicInfo INNER JOIN PolicyInfo ON BasicInfo.RegisterNumber = PolicyInfo.RegisteNumber
WHERE (((BasicInfo.Print)=Yes) AND ((BasicInfo.Company)="RFH"));

Thanks

John

 

[pbrodsky]

John

PHV means the format property of the field on the report layout. The "format" function (as used in a query) converts the value to text - which is not what you want.

 

[PHV]

Replace this:
IIf([Filed]=Yes,[TotalAmount],"-") AS TotalAmountFiled
By this:
IIf([Filed]=Yes, [TotalAmount], 0) AS TotalAmountFiled
(Similar thing to do for unfiled stuff).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Phil4tektips]

On the report you could just conditionally format the field from teh tools menu. So that a '0' is the same colour text as the background colour of the form. That way when its printed its not seen.

A budget fix!

-Phil-