Return a Zero Value

MDDDS

Registered User.
Local time
Today, 01:42
Joined
Oct 17, 2003
Messages
10
I am comparing 2 tables. The first table has a list of the states and the second has the state and # of clients. If there are no clients in a state, I need to return a zero value for that state. Can this be done?
 
MDDDS said:
I am comparing 2 tables. The first table has a list of the states and the second has the state and # of clients. If there are no clients in a state, I need to return a zero value for that state. Can this be done?

Yes it can be done, but it would be better to just calculate the value when you need it.

This is very easy to do. if you just want it in a form it is better to do it there then in a query.

Just create a text box and when in design view, type in it:
In a main form type:
=[Forms!yourClientsForm!Clients![CountOFClients]

in the footer of the clients form
=count([clients])
Name this to CountOFClients

if you want calculation results in the query

have a query where the states are in one field and your clients are in another, then click on the totals button on the toolbar.

On the states field, Change "Group by" to Count

and your done
 
Thanks, but this doesn't do the job. I want to get all the information in the query and use the query for a report. If the subscriber is a single member, the count and the sum of members are equal so no problem. With the family plan, I count the number of plans and sum the number of members. I than add the family plan members and the ind. members to get a total membership. If there are no ind. members, I use the count and a zero is ok. With the family plans the zero is in the count, but the sum comes out a blank. When the member total is created in the query for the ind. and family totals I get a blank. I need a zero in the sum column if there are no members in the state for the family plan so I get a total for the total members, otherwise states with no family plans do not get the ind.plans counted, because the family is null(?)
 

Users who are viewing this thread

Back
Top Bottom