empty COUNT query problem...

  • Thread starter Thread starter Jerome
  • Start date Start date
J

Jerome

Hi,

Hopefully somebody'll understands my description here:

I've got a form (A), representing a certain event, with a subform (B)
representing the people coming to that event and their function. Now I
want to display on form (A) the total number of people of a certain
function who have already registered.

Right now I've done the following: created a count query on the people
list (with a criteria), created a form with one field (the # result of
that query) and placed this form as a hidden subform on form (A). There,
to make it easier, I created a visible textbox bound to the total number
on that subform. That works, except for those records that don't have
any people of that certain function yet! There the textbox writes
"#error# and/or asks me to debug.

My question now is: What do I have to do that the textbox writes 0 if
the count query is null? Meaning there are no people yet with the
desired criteria. I tried iif(IsNull(...)) and things like that, but
that doesn't work!?

Any help is greatly appreciated!

Jerome
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Assuming you've correctly set up the subform control's Master/Child
links; make the subform a datasheet or continuous form. In the
subform's footer section place a TextBox with a ControlSource like this:

=Count(*)

Name this TextBox: txtParticipants. This will count the number of
participants, listed in the subform.

On the main form place a TextBox control with a ControlSource of:

=SubformName!txtParticipants

This will read & display the number of participants for the event. Be
sure to change "SubformName" to the name of the subform control.

If there aren't any participants the TextBox txtParticipants will show 0
(zero).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQJaGbIechKqOuFEgEQLpSwCdEWFOaNjnr/TLMSVP+4VKywMeE/AAoNZZ
4Y/B0RdYl6daV0TMI1LzicnI
=DtJX
-----END PGP SIGNATURE-----
 
Thanks for answering, but the problem is I don't need the total number
of all participants, I just need the total number of participants of a
certain kind (defined in one of the fields of the record).

So unless it's possible to say "Count only those participants where
status=x", I can't use your suggestion. But I guess you need a query for
such 'complex' counting? And that's what I tried with this subform I
mentioned in my first post.
 
Thanks for answering, I just tried it, but I always get an #name? error.
Of course I've replaced subformControl etc with the actual names. Does
this HasData work in Access 2000 (I forgot to mention I'm using that
version)?

Thanks.
 
the actual expression:

=IIf([fCountAttente].Form.HasData;[fCountAttente].Form!Nombre_attente;0)

the name of the subform: fCountAttente
the name of the text box of the subform: Nombre_attente

and it's ';' instead of ',' because of the regional settings.

thanks.
 
Also check to make sure the name of the subform CONTROL is fCountAttente.

--
Duane Hookom
MS Access MVP


Jerome said:
the actual expression:

=IIf([fCountAttente].Form.HasData;[fCountAttente].Form!Nombre_attente;0)

the name of the subform: fCountAttente
the name of the text box of the subform: Nombre_attente

and it's ';' instead of ',' because of the regional settings.

thanks.


Duane said:
Please reply with your actual expression as well as the Name of your text
box.
Click to expand...
Click to expand...
 
Problem solved, I got the solution in a different newsgroup:
*******************************************************************************
The HasData property applies to reports, not forms.

Try:
= IIf([mysubform].Form.RecordsetClone.RecordCount = 0, 0,
[mysubform].Form!noAtt)

Another alternative would be to use IsError() inside IIf().

In general, you only need to worry about this issue if records cannot be
added to the subform (e.g. its RecordSource is read only, or its
AllowAdditions property is No). If not, the subform goes completely blank
when there are no records to display (since it cannot show the new record),
and so attempting to refer to the non-existent control causes the error.
*****************************************************************************
 
Back
Top