Removing category that value =0

[msuguy71]

Hello, I have a question. How do I remove a category from a group that the value =0? I have a group footer that displays the values in a table (destination) then in a text box in the group footer I have a count function - =DCount("[Destination]","EMS Main","[Destination] ='" & [Destination] & "' and [Date of Service] Between Month(Now())&'/1/' & Year(Now())And Now() and [Service] ='CAEMS'")

The DCount function works great except I am not sure how to exclude categories.

The categories in the table are things like Home, HGB, ERMC, etc. Currently all categories show up. Those with no values show the category name then 0. Those with values show the category name then the count. This would be ok but I want to be able to surpress the categories that =0. Here is an example of how it works now:

Destination:
Home 0
HGB 2
ERMC 1
BCHS 0

How would I exclude Home and BCHS?

Thanks,
Rick

 

[ReVeLaTeD]

Not sure if this is helpful, but I only know how to do this in SQL.

You could theoretically get away with something simple, like (assuming "Destination" refers to the digit in your sample):

[Destination] > 0

If you put that string into the "Filter" section of your Report Properties and turn the "Filter On" on, does that work? It all depends on what you name the control where the DCount is outputting into. That's what you'd put where "Destination" is in that string.

 

[msuguy71]

I don't know anything about SQL. Also, not sure if that would work. The problem is that the category names:

Destination:
Home 0
HGB 2
ERMC 1
BCHS 0

like Home and BCHS all come from a table and I have a lookup box list them. I then use the DCount function to match them up. Wasn't sure if I could add anything to the critera of the DCount command to exclude them.

 

[ReVeLaTeD]

That's what I mean though. SQL is just another way to do what you're asking.
Here's what I mean.

The name of the box that holds the DCount...let's assume you called it "Destination". When you go into the properties of that Report (by double clicking on the small box in the upper right hand corner of Design View) you should see the Filter property I was talking about. The code I put above SHOULD work to limit the records, because it's only going to show records, COMPLETE records, where the contents of the box with the DCount are greater than 0. So it wouldn't show the text either, because that's part of the record. Does that make sense?

Ultimately what you're doing is you're telling the report (which is pulling data from the table/query) not to show records, or rows, where the contents of that one box equal zero. Isn't that what you want?

 

[msuguy71]

I have placed [Text7]>0 in the properites for the Report. Right click on the report title bar and use that properties. I get a parameter box that pops up that asks for an entry for Text7. This is the name of the textbox that contains the DCount command. This would make sense as my recordsource is the table "Destination" and there is no field in that named "Text7'. The only field in the table Destination is Destination. The Text7 field is a calculated textbox in the report only. I tried [Destination] which is the name of the database field and it doesn't work and I get a datatype mismatch.

Any more ideas?

 

[ReVeLaTeD]

I see, so you're not actually storing the output of that DCount in the table. Then in that case....what is it that the box is actually counting? The number of instances of each Destination in the table?

 

[msuguy71]

Yeah...that is what it is doing.

 

[ReVeLaTeD]

Ok, I misread your original post. My apologies. I thought that 'Destination' was the field, but you actually have multiple fields, so I was incorrect.

If you set the following code into the OnActivate property of your report. You'll need to select [Event Procedure] and put the following code in:

Private Sub Report_Activate()
If Me.Text38 = "0" Then
Me.BlankField.Visible = False
Me.Text38.Visible = False
Else
Me.BlankField.Visible = True
Me.Text38.Visible = True
End If
End Sub

And replace the "Text38" with the name of your count box for that field, and replace "BlankField" with the name of your fieldname box, that should work, IF I'm understanding the layout of your report. This is assuming:

1 - you have the field names listed as 4 separate boxes, based on your first example;
2 - you have 4 separate DCount boxes, one for each field, and
3 - you want to exclude completely any ones that are 0 value.

Ignore the "Private Sub Report_Activate()" line.

 

[msuguy71]

Ok, it worked if I put the code into the OnFormat event property for the desination group footer. The code removes all the destinations that had zero but there is an unanticipated problem. I did not realize that the fields are still there just not showing. The report now looks like this:

Destination:
-----------------


HGB 2



ERMC 2



The categories are separated by the invisible ones. Any way to get rid of all the white space?

Thanks for the code though.

 

[ReVeLaTeD]

Yeah, that shouldn't be too difficult, but it's tedious.

Step 1:
Change the CanShrink and CanGrow properties of ALL boxes to Yes.

Step 2: Change the Height property of ALL boxes to 0". This is where it gets a little tedious, but you'll appreciate the final output.

Step 3: in Design View, the boxes should now just be horizontal lines that you can barely see, if at all. You'll need to shift these up and close to each other. What this will do is, any boxes that have a 0 value and are thus hidden will just be part of the natural spacing. The closer together you can put them, the neater it will look when you preview the report.

When you look at the report now, everything should be lined up together. The box with the zero balance should just be blank space, since the "Visible" property is set to False.

 

[msuguy71]

I only have two boxes...one that reads the destination from a table and one that counts the number of incidents in the table (the DCount function). I do not have a separate box for each destination. Will the above still work? Thanks.

 

[ReVeLaTeD]

Hmmm. No. That's a very interesting database you have there.

What you could do though, if you don't care about the sort order of the Destinations, is sort by the DCount column maybe? I'll tell you that if the order of the named Destinations is important, than I'd have to see a shell sample of your database in order to see exactly how you have everything set up.

If it doesn't really matter, then you might be able to sort by the DCount box. That box has to have a name, then you set it up under "Sorting and Grouping" under the View menu, pick the name of that box, and sort Ascending. That will always make sure that the zero value boxes are the top of the list. You'll still have a space at the top,and if it's just one field, it makes it harder. But at least it won't be spaced out between Destinations.

If it does matter...then I admit defeat without seeing the database for myself to know how you have everything laid out.

 

[msuguy71]

I don't have a problem sending out the database. I am open to all suggestions...I am still learning how to use advanced stuff in Access

 

[ReVeLaTeD]

If you could zip and email the database, I can take a look at it and get back to you. I'll need to see how you have everything laid out. Hopefully it shouldn't be too big once you zip it. firstamericanstar@cox.net is my email.