If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
=iif Calculated field in report?
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 |
#2
|
|||
|
|||
=iif Calculated field in report?
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 |
#3
|
|||
|
|||
=iif Calculated field in report?
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 |
#4
|
|||
|
|||
=iif Calculated field in report?
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 "scottyboyb" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|