Re: IIf or Nz function

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Eureka! The zero-count expression worked. I believe that subform is read-only
because the underlying query is based on summed data.
Thanks Great One.


"Allen Browne" wrote:

Joe, I am confused about where the problem lies: on the report, or on a
form. If it is a report with a subreport, the article will solve the
problem. If it is a form with a subform, it will not help.

From your example, it looks like TextTTL is a text box in a subform. Since
you say it contains a total, I will assume that TextTTL is in the Form
Footer section of your subform (not page footer, or detail section.) I will
also assume that it contains a numeric calculation. To help ensure that
Access understands the data type, set its Format property to General Number
or Currency of something numeric.

Now, the text box on the main form bound to:
=[qryNR subform1].[Form].[TextTTL]
works fine when there are records. Set its Format property as well.

When there are no records in the subform, one of 2 things happens:
a) the subform shows a new record, or
b) the Detail section of the subform goes competely blank.
(b) happens if new records cannot be added to the subform (e.g. because its
source is read-only, or because its AllowAdditions property is set to No.)

If your case is (a), the value of the text box is Null, and Nz() solves it.
If your case is (b), referring to the non-existent control in the subform
results in an error. To avoid the error, you need to use IIf() to handle the
zero-recordcount as a special case, so the Control Source would be:
=IIf([qryNR subform1].[Form].[RecordsetClone].[RecordCount]=0, 0,
Nz([qryNR subform1].[Form].[TextTTL],))

There is another factor: the Name of the subform control may not be the same
as the name of the form it contains. To check its name, open the main form
in design view, right-click the edge of the subform control, and choose
Properties. On the Other tab of the Properties box you see the Name of the
control. Its SourceObject property indicates what form gets loaded into the
control.

If it is in fact a report with a subreport, using HasData instead of testing
the RecordCount of the RecordsetClone achieves the same result.

If you are still stuck, you can debug it by opening the Immediate Window
(Ctrl+G) while the form is open, and enter something like this:
? Forms![Form1]![qryNR subform1].[Form].[TextTTL]
By taking a bit at a time, you may be able to identify which part of the
name is wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JoeA2006" <JoeA2006@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3252D774-7628-402C-B8CE-CC5CA19CF195@xxxxxxxxxxxxxxxx
If I test this with data
=[qryNR subform1].[Form].[TextTTL]
I get a total in the Text box

When I test with IIf function with data I get
#Name?

=IIf([qryNR subform1].[Form].[HasData], [qryNR subform1].[Form].[TextTTL],
0)
What is different?



"Allen Browne" wrote:

See:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

The article explains how to test the HasData property of the subreport,
and
why that's necessary.

"JoeA2006" <JoeA2006@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9AB35B05-5D1C-4E4D-9CF2-410747A0A6FA@xxxxxxxxxxxxxxxx
I need to use a total from a subreport that may or may not have data for
a
calculation. The Nz and IIf functions seem to work when the subform
show
up
on the main form, however one form does not show up and when I use the
IIf
or
Nz function I still get an error. Why do other objects show and this
one
does
not?The calculation seems to work on the accompanying report, but will
not
work on the main form.



.

Relevant Pages

  • Re: Run-time error 2467... Oh yes it does!
    ... Rick and Others, ... configuration and get the same error when I try to input data to a control on ... my subform. ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.formscoding)
  • Re: Combo box will not select values
    ... assuming that the name of the subform control is. ... "Rank" combo box on the main form to cboRank, as a field and combo box have ... >> a the rank of the new scout selected) ...
    (microsoft.public.access.modulesdaovba)
  • RE: Child control is changing data on exit, how to stop?
    ... that means the Control Source for the control is ... The subform has Office Name as the link child and master fields. ... Does not position the form to the selected doctor? ... what else do I have to add to get this cbo to filter the subform ...
    (microsoft.public.access.forms)
  • Re: Subform Totals
    ... incompatible with an expression inside a control. ... For each of these fields their is a total field in the form footer. ... form footer of the subform or about the form footer of the main form. ... The questions is why do the total fields in the form footer of the sub ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Lookup record in subform based on combo im main form
    ... rather to it's control source. ... buttons on the subform. ... RowSource: Facilities ... Control Source = EventID -- This is the primary field in the Events ...
    (microsoft.public.access.formscoding)