How to skip certain groups based on a condition 1

[itmasterw]

Hi
I have a report that is first grouped by Store name and then by District number, and then store numbers; and showing the sales for each month like this:
Tops
District # 1451 Jan Feb March
Store #
1456 100 125 214
2569 125 241 1254

Total 225 366 1468

District Summary
1456 100 241 124
2569 125 314 110

Total 225 555 234

Target
District # 2451
Store #
1446 180 214 0

Total 180 214 0

District Summary
1446 180 214 0

Total 180 214 0

The problem is that they want it to only show the summary for the groups that have more than one store listed. I was told that there is a way, through VBA coding, of setting a counter, that if there is more than one store to go to the next, and the in the event tab the headers property section to out in some code that access this; but I am not sure how to do this. Also they said to look into On Group header, but I could not find anything.
If anyone can help me with this I would appreciate it.

 

[bitmite]

in the group properties there (where you select your grouping and sorting) the is a number that indecates the minimum size of a group try that number and set it to 2 see if that works. never done it before but might work.


Brought to you By Nedaineum
The Portal to Geek-tum

 

[yoshi88]

Hi, you can add a field name Count with the source =count(*) in the group footer. Then in the format event for the detail put this code:

If Count < 2 Then
Detail.Visible = True
Else
Detail.Visible = False
End If


With that code the detail of the groups with more than one store wont be visible. The only thing visible will be the Count field, you can change the properties for not visible. After that add your Total in the group footer.

Hope this will help


Frank

 

[itmasterw]

No that did not do it, but thanks for trying. IF you have any other ideas on this I would appreciate it.
Thank You

 

[itmasterw]

Hi Frank when I put that in I get an error teling me that the Macro does not exist. What do think is happing here, or am I doing something wrong?

 

[itmasterw]

Thanks, I put it in module and it worked.

 

[yoshi88]

I'm glad it's working.

Frank