Calculated Controls (Access 2003)

[deltasmith]

I have a Invoice Entry form with 4 Subforms; When I print the Invoice Report also with 4 SubReports, I get error messages on totals where there is no entry. I am sure that it is my lack of knowledge which is causing this. When I have entries in all of my subforms, I get no error messages; or if I enter 0 and $0.00 then I do not get an error message in my totals; however the underlying table has a 0 for an entry which is something I wish to avoid. Maybe I need to enter an Iff statement, however I do need to enter one which will when "Y" for warranty is entered it will return 0 for a total. Can any help me figure out why I am getting an error on the totals in my Invocie Report when there is no entry in a Subform? Thank you, I am in the midst of losing whatever it was I knew. Delta

[HansV]

Do yo want to refer on the main report to totals on the subreports? If so, try something like

=IIf([SubReportName].Report.HasData,[SubReportName]![txtTotal],0)

This checks if the subreport named SubReportName has data. If so, it returns the value of the text box txtTotal from the subreport, otherwise, it returns 0.

[deltasmith]

Thank you Hans, that worked great. I have been going through the books I have and am unable to find how to add an "or IIf" to state IIF Warranty = Y then 0. Warranty is in the same subreport as the first IIf Statement The IIf statement took out my totals on the main Report which totals from the four subreports, but I will deal with that after I get the rest of this working. I am new at this and trying to take it slow. Thanks again. Delta

[HansV]

Not sure what you mean, but you can do things like this:

<code>=IIf((Condition A) Or (Condition [img]/forums/images/smilies/cool.gif[/img], Value_if_A_or_B, Value_if_Otherwise)</code>

and

<code>=IIf(Condition A, IIf(Condition B, Value_if_B, Value_if_Not_[img]/forums/images/smilies/cool.gif[/img], Value_if_Not_A)</code>

[deltasmith]

=IIF([sbrpt Sublet].Report.HasData, or [sbrpt Sublet].Report![Warranty]=y,[sbrpt Sbulet]![Sublet Cost],0)
If Subreport Sublet has Data or Subreport Warranty = y, then Sublet Cost is 0

This of course is not working, but is close to what I want to say. Warranty is a field in the sbrpt Sublet
Thanks for your help
Delta

[HansV]

Try this:

<code>=IIf([sbrpt Sublet].Report.HasData, IIf([sbrpt Sublet]![Warranty]="y", [sbrpt Sublet]![Sublet Cost], 0), 0)</code>

I have assumed that the value y is a text value, so I enclosed it in quotes.

[deltasmith]

Hi Hans, I have tried your Iff statement, and Yes the "Y" is a text field, however I keep coming up with an error. I just can't figure out what I am missing. If you have any words of wisdom I would greatly appreciate them.
delta

[HansV]

Check carefully that all names used in the expression are correct; the simplest typo will cause an error. In particular, you must use the name of the subreport as a control on the main report. This is not necessarily the same as the name of the subreport in the database window. You can see the control name by opening the main report in design view and clicking once on the subreport. (Don't click on it a second time, you will select something on the subreport then.) The control name will be displayed in the Controls dropdown box on the Formatting toolbar, and in the caption of the Properties window.

[deltasmith]

Once again, you came through, I had a typo, and even my calculations are working on the main form. I really appreciate all of your help. Delta

[wmfay1]

Used to be that you could use a PUBLIC variable from the MODULE of a FORM or REPORT as a component of a calculated control. What gives? Access now reports #name?.
Are we having yet another Holy War over what is safe and unsafe, or was this an oversight during yet another re-write?

[HansV]

What are you ranting about? As far as I know, it has never been possible to refer directly to VBA variables in expressions. You can create a custom function to get the value of a variable, and use that in expressions.

[wmfay1]

Figured a quick fix to the problem.
Add a simple PRIVATE FUNCTION to the MODULE which returns the value of the variable.

Private sgValue As String
Private Function getValue()
getValue = sgValue
End Function