counting entries in a query & dispalying in a form

[avayaman]

I have a database for a club and in the swithcboard are displayed several at a glance stats. In the forum is a status field (called status) for active,deceased, expired etc. There is also a date that shows their membership expiry date.(called DuesPay)

I have query called qrystats with series of columns containing a field each condition (Active, deceased, etc) For exmple:

Deceased Field: Sum(IIf([status]='Deceased',1,0))

On the switch board is a display box with a control source

=DSum("Deceased","qrystats")That box displays the correct number

Now comes the problem. I want to display a box showing the number of members coming up for expiry in the following month. I have already created a query to show this with the filed DuesPaid, criteria Between DateSerial(Year(Date()),Month(Date())+1,0) And DateSerial(Year(Date()),Month(Date())+2,0)

With the other fields I wish to show in the remaining column. This works fine.

I cannot figure out how to create another query which will count the number of entries in the query above so I can display it on the switchboard.


Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[lameid]

You would either use count instead of sum around the same fields or count(*) if you wanted to count records that met the criteria (Count does not count nulls).

Although since you are wanting to use dsum, why not use Dcount instead?

 

[avayaman]

Aha! Dcount worked. Always something simple.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com