Re: =iif Calculated field in report?

Tech-Archive recommends: Fix windows errors by optimizing your registry

First, thank you John!

I had to change the syntax a little to:

=[Forms]![Print Invoice]![Total Expenses]+[Forms]![Print Invoice]![Total
Hourly Billings]-IIf([Forms]![Clients]![Clients
Subform]!HasData,[Forms]![Clients]![Clients Subform]![Total Payments],0).

The original change you provided gave me a "#name!" error. I checked and
re-checked the field and form names and I could not find a mistake, so tried
the "!". But it does cause a little peculiarity. When I have data in the
payments field, I get the total just right. When it is empty, I get nothing
in the total field. Curiouser and curiouser. Any thoughts.?

By the way, you asked about the form fields and the report. I am using the
form's data to provide parameters for the report. For instance, If I have a
particular client's record and project record visible on the form/subform,
then that is data that goes to the invoice. I cannot take total credit for
this. I started with a copy of the time and billing template from Microsoft
as the basis for this project. They set it up this way to start.

Best,
Scott B


"John Spencer" wrote:

Use the HASData property of the subreport or subform. You talk about a report
and then you seem to be referencing a subform.

=[Forms]![Print Invoice]![Total Expenses]
+[Forms]![Print Invoice]![Total Hourly Billings]
-IIF([Forms]![Clients]![Clients Subform].HasData
,[Forms]![Clients]![Clients Subform]![Total Payments],0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

scottyboyb wrote:
Greetings,

Access 2000 on Windows xp sp3.

I have an invoice report with three sub reports: 1, time worked, 2, expenses
& 3, payments. I have a calculated field that adds the subtotals from each
subreport and places it at the end of the report. Here is the expression
rigth now:

=[Forms]![Print Invoice]![Total Expenses]+[Forms]![Print Invoice]![Total
Hourly Billings]-[Forms]![Clients]![Clients Subform]![Total Payments]

This works fine if I have data in all three subreports. But not if the
payments subtotal is empty. I have not tried it with any other empty
subreports. Right now I only have the circumstance that payment is null.

How do I write the =iif statement so that I get the result of all three if
all three have data and the results if one or both of the other two does not
have data?

I have situations where I only am invoicing time or only invoicing material
but I do not invoice for payments.

Many thanks,
Scott B

.

Relevant Pages

  • Re: =iif Calculated field in report?
    ... John Spencer has started me well in the right ... payments field, I get the total just right. ... you asked about the form fields and the report. ... I have an invoice report with three sub reports: ...
    (microsoft.public.access.reports)
  • Re: create totals based on a field values...
    ... In general an invoice is no more final than ... form or report calculations to display those values ... Payments table. ... >doesn't change therefore the totals don't change... ...
    (microsoft.public.access.queries)
  • Re: report group subtotals incorrect
    ... payments for the same invoice... ... Query is below. ... > report is then bound to a query that lists only the invoices, ...
    (microsoft.public.access.queries)
  • =iif Calculated field in report?
    ... I have an invoice report with three sub reports: ... This works fine if I have data in all three subreports. ... payments subtotal is empty. ...
    (microsoft.public.access.reports)
  • Re: Report for each individual record
    ... On Jul 20, 2:49 pm, John W. Vinson ... I am going to create a Report ... every time I do something with the Invoice it relates to all the records - ...
    (microsoft.public.access.gettingstarted)