Report won't print zeros 1

[jason246day]

I have a report that counts the number of servers in each environment. But the problem I was running into was that when an environment had no servers in it, the report wouldn't even list it with the other environments. To get around this, I created a report that lists out all the environments, and a subreport to count all the servers each environment. This works great, except I can't get my subreport to print a zero when there are no servers. It just leaves a blank next to the environment name, rather than a number. I tried using the Nz() function in the control source, but it didn't do anything. Does anyone have any suggestions as to how I can get a zero to show up in the blank? Or even a way to create this report without having to use subreports?

Thanks ahead of time.
Jason

 

[hneal98]

are you putting an '=' before the statement in your control source? It should be the first thing there. Also, change the name of the field to something other than the Table field name. Let me know if you don't understand what I am talking about.

 

[jason246day]

Here is what I have in my control source in the subreport. This is the only thing on this report.
=Nz(Count([AllocationID]),0)

And the name of this field is Allocation.

 

[hneal98]

Does the control have the same name as the field it is based on from the table? If so, try giving it a different name.

 

[jason246day]

No it doesn't, the name of the table field it is based on is AllocationID, which is what I am counting. It works for every other environment, except for the ones that have zero servers.

I think the problem could be caused by me using a subreport, but if i don't use one, I can't get Access to show the environments that have zero servers in them. And the whole point of this report is to show every environment, and the count of servers each has. Do you know of a way to make this happen without subreports?

 

[hneal98]

You could create a table that has the names of all the environments, then just use a left join in a query to do the counts. The table with the environment names would be on the left side of the query and the servers you are counting would be the right side of the query. Your query would essentially say give me every environment reguardless of whether there are any servers to count. Then you can count them and still have them all show.

Does that make sense? I am not sure if I explained that clearly.

 

[jason246day]

Yes that makes complete sense, its actually what I have been trying to do all along, but couldn't get it to work.

How do I go about creating a query like that?

 

[jason246day]

Thank you so much!!!!

I understand what a left join is, I just wasn't sure how to create that in Access. But I figured it out. Thank you so much for your help!!!

Jason