A field in report shows a blank instead of zero

[Jimcb8]

I created a query with two colums; one column is the count the number of records and the other column sums another field based upon a certain criteria being met.
This information is printed in a report.(actually a subreport of the main report)

If no records in my database meet the defined criteria:
the coumn with the count shows a zero (0) my other column shows only a blank.
How do I get boths columns to print a zero in that situation?
Example:
query counts the number of familiies who live in Buncombe county as the first column and the second column is the sum of all of the members in each family.
If the criteria is met i.e. there are families who live in Buncombe county the report shows:

5 families 20 induviduals

If criteria is not met the report shows

0 families (blank) individuals

I tried NZ in the sum thinking it was null but that is not the case

Your thoughts are appreciated

 

[GanzPopp]

You need to give some more information. The query you use would be useful and the field types of the field you are selecting in this query as well.

 

[Jimcb8]

SELECT Count([Client Information].County) AS Transylvania, Sum([Client Information].[#HouseholdMembers]) AS [Transylvania#]
FROM [Distinctfamiliesinmonth/year] INNER JOIN [Client Information] ON [Distinctfamiliesinmonth/year].Clientid=[Client Information].ClientID
HAVING ((([Client Information]![County])="Transylvania"));
#HouseholdMembers is long integer, zero decimal places
TheTranslvania# is the field that does not show a zero
Thanks so much for your help, much appreciated
Jim

 

[GanzPopp]

Please encapsulate your SQL-code in code tags: [ code] and [ /code] (without spaces). This makes it easier to read.

I changed little bit to your query code:

[SIZE=3][FONT=Calibri]SELECT Count([Client Information].County) AS Transylvania, Nz(Sum([Client Information].[#HouseholdMembers]),0)) AS [B][Transylvania#][/B][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]FROM [Distinctfamiliesinmonth/year] [B]
LEFT JOIN[/B] [Client Information] ON  [Distinctfamiliesinmonth/year].Clientid=[Client Information].ClientID[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]HAVING ((([Client Information]![County])="Transylvania"));[/FONT][/SIZE]

Since an INNER JOIN would not return results at all (not even NULL) a LEFT JOIN is needed, so that at least NULL is returned. NZ is used with the SUM and because SQL turns the result into text, a VAL function was added to make sure it's a number.

To be honest, I would recommend doing this differently. To get the number of households per county create the following query (called 'qrySumPerCounty'):

[SIZE=3][FONT=Calibri]SELECT NZ([Client Information].County, "(Unknown)") AS CountyName, COUNT([/FONT][/SIZE][SIZE=3][FONT=Calibri][SIZE=3][FONT=Calibri]CountyName) AS CountyCount, [/FONT][/SIZE]
Nz(Sum([Client Information].[#HouseholdMembers]),0)) AS [B][Transylvania#][/B][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]FROM [Distinctfamiliesinmonth/year] [B]
LEFT JOIN[/B] [Client Information] ON  [Distinctfamiliesinmonth/year].Clientid=[Client Information].ClientI[SIZE=3]D[SIZE=3] 
GROU[SIZE=3]P BY [/SIZE][/SIZE][/SIZE][/FONT][/SIZE][SIZE=3][FONT=Calibri][SIZE=3][SIZE=3][SIZE=3][FONT=Calibri][Client Information].County[/FONT][/SIZE];[/SIZE][/SIZE][/FONT][/SIZE]

In another query you can sum the result with your conditions:

SELECT [SIZE=3][FONT=Calibri]CountyCount, [Transylvania[SIZE=3]#[/SIZE]] FROM qr[SIZE=3]ySumPerCounty WHERE [/SIZE]CountyName[SIZE=3]="Transylvania";[/SIZE][/FONT][/SIZE]

Hope this helps! Best practice is to first split your query into multiple smaller ones. Once they produce the desired results, you might want to merge them into one big query.

 

[Jimcb8]

Thank you so much I will try this immediately.
I really appreciate helping me with this issue because was I really stuck

Jim