Show group of fields only if one Not 0 1

[Fozzy9767]

I have a table with the following structure:

Sortcode | Merchant Number | Item1Count | Item1Cost | Item1Exp | Item1Rate | Item1Net etc..

There are several Items (they are not named this way, this is just an example), The key field does end in "Net". I need to create a report that will display only groups of "Items" where the field ending in "Net" is anything other than "0". In ither words, I don't want to fill the pages up with fields that do not have a "Net" amount that is either positive or negative. I currently have 127 different "Items". But for any given report I may only need to disply 60-75 of them. I also have this broken down into several tables for simplicity (and the fact that Access cannot handle more than 255 fields at a time)

I know I can make a sub-report not visible, but I'd not sure creating 127 individual subreports would be very efficient. Any suggestions on how to proceed will be appreciated.
Ken

 

[dhookom]

Read up on normalization so you can fix your table structure. Here is a link to one of many very good pages

http://www.datamodel.org/NormalizationRules.html.

I personally wouldn't do any more work until the tables were normalized. If you can't normalize then you should create a union query.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Fozzy9767]

OK, after I wrote this I saw someone else with a similar project. So after reading that thread I came up with this code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Ctl As Control
Dim strTag As String
For Each Ctl In Me.Detail.Controls
If Ctl.Name Like "*Net" Then
If Ctl.Value = 0 Then
Set strTag = Ctl.Tag
For Each Ctl In Me.Controls
If Ctl.Tag = strTag Then
Me.Ctl.Visible = False
End If
Next Ctl
End If
End If
Next Ctl
End Sub

This isn't working, but I think I'm getting close. Anybody have some input?
Ken

 

[Fozzy9767]

Duane, I'm sorry you seem hooked on this "normalization" answer, it has nothing to do with this question. People keep assuming that my tables are not normalized. Remember what happens when you assume. What makes you make this assumption? If it was the example field names I posted, I thought I made it quite clear they were examples only. The question I now am asking is where the problem is in my code, When I test it I get nothing but blank pages. I made a small change, adding a "Else strTag = """ line int the code that checks for a 0 value, but that made everything display. I'm just testing with three items at the moment for simplicity's sake.
Ken

 

[dhookom]

If you are describing a table, I still assume your table is not normalized. I don't care what your actual field names are.

However, you don't have any code lines that would set any controls back to visible. Add debugging code like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Dim Ctl As Control
  Dim strTag As String
  For Each Ctl In Me.Detail.Controls
    If Ctl.Name Like "*Net" Then
      If Ctl.Value = 0 Then
        strTag = Ctl.Tag
        debug.print Ctl.Name, Ctl.Tag
        For Each Ctl In Me.Controls
          If Ctl.Tag = strTag Then
            Me.Ctl.Visible = False
            Debug.Print "--" Ctl.Name, Ctl.Tag
          End If
        Next Ctl
      End If
    End If
  Next Ctl
End Sub

Then open the debug window to check the results (press ctrl+g).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Fozzy9767]

Hi Duane, I have read that link about normalization. This table has no duplicated data ata all. Every field is distinct and unique. It is actually the result of a query that I changed to a make table for a couple of reasons, one, it runs very slow, two, I need to be able to archive the end results, so I thought copying a table of end results would be a better way. Anyway, I inserted your debugging code and seem to still be missing something. Originally I have all my controls set to Visible = True. I can either get everything showing or nothing. Does it make a difference if my *Net field is currency? Should I be matching on $0.00? Here is where I am at now, this code makes everything visible except the bottom row of controls:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Ctl As Control
Dim Ctl1 As Control
Dim strTag As String
For Each Ctl In Me.Detail.Controls
    If Ctl.Name Like "*Net" Then
        If Ctl.Value = 0 Then
            strTag = Ctl.Tag
            Debug.Print Ctl.Name, Ctl.Tag, Ctl.Value
                For Each Ctl1 In Me.Detail.Controls
                    If Ctl1.Tag = strTag Then
                        Ctl1.Visible = False
                        Else: Ctl1.Visible = True
                            Debug.Print "--"; Ctl1.Name, Ctl1.Tag, Ctl1.Visible
                    End If
                Next Ctl1
        End If
    End If
Next Ctl
End Sub

 

[dhookom]

Did you open the debug window to see the results? Were they what you expected? Is it possible the *Net text boxes might be Null rather than 0?

You have never explained what kind of values you are using in the Tag properties.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Fozzy9767]

I think I have found part of the problem, but I'm not sure how to fix it. I am using my code in the Detail Format event and All Values are 0 as I don't believe the data is populated yet. I'm going to try a different event to see if I can get it to work properly.

 

[Fozzy9767]

Yes, the debug window is showing all values as 0 and setting all visible properties to False. I have no Null values, this is a currency field(always), so if it doesn't have an amount it will be 0. Do I need to match for currency formatting? As to Tag properties I simply assigned a name that won't conflict with field name or control names that is still relevant to the data being presented, I suppose it doesn't matter, I guess I could use Tag1, Tag2 etc... or some similar scheme. It's all working except it is matching 0 and I know they are not all 0 in the data.

 

[dhookom]

If "All Values are 0" then all controls should be invisible, correct?

I assume you have set the same Tag property value for a "group" of controls that you want to hide/display together.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Fozzy9767]

Yes, that's exactly how it is working. Since all values are 0 it makes everything invisible. Yes I used the same tag for each group of controls. But in my data not all values are 0, so either I am checking for the wrong thing and getting a false positive, or there is no data during the detail_Format event. I don't know if this is even possible, if I can get it to make selected groups of controls invisible, can I make the rest of the controls consolidate on the report so there are no gaps? Will I need to do a bumch of repositioning?

 

[dhookom]

When you state "Since all values are 0 it makes everything invisible" are you referring to the debug window or your actual data?

I would change the code to:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Ctl As Control
Dim Ctl1 As Control
Dim strTag As String
For Each Ctl In Me.Detail.Controls
    If Ctl.Name Like "*Net" Then
        strTag = Ctl.Tag
        For Each Ctl1 In Me.Detail.Controls
            If Ctl1.Tag = strTag Then
                 Ctl1.Visible = (Ctl.Value <> 0)
            End If
        Next Ctl1
     End If
Next Ctl
End Sub

The controls will not "consolidate" without you adding more code. This entire visibility and consolidation solution might be a no-code method if the tables were normalizable. You would then use subreports or whatever.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Fozzy9767]

I set the "can shink" property to yes and they do consolidate. Yay! Now to see if I can get this code to work. In my data there are non 0 values. When running the code everything was displaying as 0. This is obviously not giving correct results. I will try your modification.

 

[Fozzy9767]

This is working very well, it is very wonderful. You have no idea the problems this has solved. Imagine having 196 agents you need to generate reporting for and having to maintain 3 sets of reports in about 27 different versions depending on what they should see. This means I can now have one report to maintain, period! I can generate these on a merchant level, then switch the data source and generate office level reporting from the same data on the same report. So now when I need to add an item, I add it in 1 report.

I'd still be interested to know how to further normalize my data, I've now got it down to where the bulk of my data is distilled down to 5 tables, 1 for volume, one for income, 3 for expenses. I plan on making a report for each one and then integrating tham as sup-reports on a master. Considering the mess I had to start with I think this solution will be leaps and bounds better.

 

[dhookom]

My bill is in the mail ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]