=iif Calculated field in report?

[scottyboyb]

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

 

[John Spencer]

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]

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

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

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

 

[scottyboyb]

Good morning all,

As you csan see below, John Spencer has started me well in the right
direction, but I am still havng a pecuiliar artifact. Can does anybody give a
hint as to what is causing a blank field when no data is available?

Many thanks,
Scott B

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

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

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