How to show BLANK (null) counts as ZERO "0"

[KingOf206]

so i have multiple queries that are tied to a report.



Here is an example of the report i have, as you can see i have some values that don't have any number tied to it, specifically look at the value in the red box:








The values from the red box are coming from this query:








Currently there are no results and when i run the query it looks like this:







My question....

How can i get this query to show a total count of 0 ? instead of having nothing coming up? i have about 4 other queries ill need to do this too, but hopefully if i can convert this one, i can replicate it for the rest.

Id like my final report to have all zeros to show the correct number. it doesn't look professional with the number being blank.

[JoeM]

How is your Report getting its data from 4 different queries?
Are they Subreports, or are you using a lookup formula?
So, I guess what I am really asking is what is the Control Source of that field?

Sometimes, you can use the NZ function to convert Nulls to Zeroes, but it depends on how you are linking to the data.
See: https://www.techonthenet.com/access/...dvanced/nz.php

[KingOf206]

How is your Report getting its data from 4 different queries?
Are they Subreports, or are you using a lookup formula?
So, I guess what I am really asking is what is the Control Source of that field?

Sometimes, you can use the NZ function to convert Nulls to Zeroes, but it depends on how you are linking to the data.
See: https://www.techonthenet.com/access/...dvanced/nz.php

My final report uses all sub reports based off the corresponding queries. Trying to figure out how i can modify the queries to show 0 instead of blank boxes. i am not sure how to use nz functions in my query

[JoeM]

OK, I don't think you are going to be able to use NZ in a Subreport structure like that.

I think you will need to change how your queries work, because you need to get a line of data to return with "KING" on it.

Here is one way to do that.
Instead of putting Criteria like that, I would recommend creating a one-line table that has the Criteria value you are looking for.
Then do a Left Join from that to your Data Table/Query to return the Count.

So, in this example, I have a table named CriteriaTable with one field named CriteriaValue. Their is one record in this table, with the value of "King".
I then have my data table, named DataTable, which has your fields.

So, the SQL code of that query that returns the counts, regardless if it is zero or something else would look like:

SELECT CriteriaTable.CriteriaValue, Count(DataTable.szSentToRcvdFrom) AS CountOfszSentToRcvdFrom
FROM CriteriaTable 
LEFT JOIN DataTable 
ON CriteriaTable.CriteriaValue = DataTable.szSentToRcvdFrom
GROUP BY CriteriaTable.CriteriaValue;

So, I would just do something like that for all four queries.

[CJ_London]

you can use the format property in a form or report control


for numbers there are 4 sections - positive; negative; zero; null

for text there are two hastext;null

for numbers to display the word 'Zero' for 0 and null you would use

;;"Zero";"Zero"


you can also use a limited range of colours, for example

0;[red](0);[Blue]"Zero";[Blue]"Zero"

will display negative numbers in red and surrounded by brackets

for text you would usually have something like

@;"Please enter some text"

[KingOf206]

you can use the format property in a form or report control


for numbers there are 4 sections - positive; negative; zero; null

for text there are two hastext;null

for numbers to display the word 'Zero' for 0 and null you would use

;;"Zero";"Zero"


you can also use a limited range of colours, for example

0;[red](0);[Blue]"Zero";[Blue]"Zero"

will display negative numbers in red and surrounded by brackets

for text you would usually have something like

@;"Please enter some text"

I am in the sub report property.. i dont see the 4 sections. Where is that at?

[KingOf206]

OK, I don't think you are going to be able to use NZ in a Subreport structure like that.

I think you will need to change how your queries work, because you need to get a line of data to return with "KING" on it.

Here is one way to do that.
Instead of putting Criteria like that, I would recommend creating a one-line table that has the Criteria value you are looking for.
Then do a Left Join from that to your Data Table/Query to return the Count.

So, in this example, I have a table named CriteriaTable with one field named CriteriaValue. Their is one record in this table, with the value of "King".
I then have my data table, named DataTable, which has your fields.

So, the SQL code of that query that returns the counts, regardless if it is zero or something else would look like:

SELECT CriteriaTable.CriteriaValue, Count(DataTable.szSentToRcvdFrom) AS CountOfszSentToRcvdFrom
FROM CriteriaTable 
LEFT JOIN DataTable 
ON CriteriaTable.CriteriaValue = DataTable.szSentToRcvdFrom
GROUP BY CriteriaTable.CriteriaValue;

So, I would just do something like that for all four queries.

I dont think i can do that. The reason is because all of my data is coming from tables that are linked to 3 different servers (which i have 0 access to). and to make things even more complicated.. all of my queries are based off of 1 master query. WHy do i do that? because the tables contain all data from all "Seasons" when i do reporting i only need the information from 1 season and a time, so my master query has the criteria i need that pulls from Season "10" then after a couple months, i change it to season "11" so that i dont have to update a billion queries, i only need to update 1. Does that make sense? lol if not let me know. but using your idea, i think it might ruin the easy way that i pull my data

[CJ_London]

I am in the sub report property.. i dont see the 4 sections. Where is that at?

Like this



note: I threw this image together to show where it is - the construct should be per my original email

[KingOf206]

Like this



note: I threw this image together to show where it is - the construct should be per my original email

I tried this on 2 of the sub reports... I clicked the text box that will show the count and i typed in exactly what you have on the format line. it still gave me a blank value. Did not show "0"

[CJ_London]

provide a screenshot like mine to show me what you have done.

[KingOf206]

Her eis 2 different rub reports. each will show the deisign and output for each. one shows the words Zero while the other shows blanks

Example 1 design :





Output:




Example 2 design :




Output:

[CJ_London]

as I said in my post 'note: I threw this image together to show where it is - the construct should be per my original email' - which is

;;"Zero";"Zero"

[KingOf206]

as I said in my post 'note: I threw this image together to show where it is - the construct should be per my original email' - which is

;;"Zero";"Zero"

Ahh ok, that did the trick.. But it did not solve my problem

HOWEVER... 2 things have occurred:

1 - The code changed it self, I don't want it to say "Zero" i want it to say "0" and when i changed the words from "Zero" to "0" the code changed to this: ;;\0;\0

2 - I now have a new issue... when i plug that formula into another the formatted field 'Forwarded to other counties" ( see #1 post in this thread). This already has a value of "1". But when i put the code in that you gave me, the 1 disappeared and when i run the report the new value is BLANK. This makes me think the code you provided wont work if there is a value there.

What solution will fix this?

[CJ_London]

for future reference see this link about the format property

https://msdn.microsoft.com/VBA/Acces...roperty-access

The code changed it self, I don't want it to say "Zero" i want it to say "0" and when i changed the words from "Zero" to "0" the code changed to this: ;;\0;\0

it should still work, it is because you are not displaying text (i.e. 'zero')

This already has a value of "1". But when i put the code in that you gave me, the 1 disappeared

because you have not formatted the positive (or negative) values, try 0;0;0;\0

[KingOf206]

for future reference see this link about the format property

https://msdn.microsoft.com/VBA/Acces...roperty-access

it should still work, it is because you are not displaying text (i.e. 'zero')
because you have not formatted the positive (or negative) values, try 0;0;0;\0

Can i use 0;0;0;\0 for all of the sub reports? i feel like i should be able to have the same formatting in all the subreports correct?

I have inserted 0;0;0;\0 into all the sub reports. so far it seems to work good!