Problem filtering detail records

[toolsgg]

In a report, I want to be able to supress the details section if the total
for the group is less than a certain value. If there are morte than one
detail record it works fine, but if there is just one detail record in the
group Access seems to ignore the code. I have debugged it and watched it
execute the code, but when you look at the report, the group is not
suppressed. Here is the code I am using in my group footer:
If Val([txtTotalHours]) > 0.166 Or Forms![Billing
Worksheet]![chkInclude10] = -1 Then
Me.Detail.Visible = True
If [txtDetailCount] > 1 Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
Else
Me.Detail.Visible = False
Me.GroupFooter0.Visible = False
End If
Anyone have any ideas how to make this work?

 

[Marshall Barton]

In a report, I want to be able to supress the details section if the total
for the group is less than a certain value. If there are morte than one
detail record it works fine, but if there is just one detail record in the
group Access seems to ignore the code. I have debugged it and watched it
execute the code, but when you look at the report, the group is not
suppressed. Here is the code I am using in my group footer:
If Val([txtTotalHours]) > 0.166 Or Forms![Billing
Worksheet]![chkInclude10] = -1 Then
Me.Detail.Visible = True
If [txtDetailCount] > 1 Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
Else
Me.Detail.Visible = False
Me.GroupFooter0.Visible = False
End If
Anyone have any ideas how to make this work?

You have that code in the group Footer? But that's too late
to influance the details in the group. It should be in the
group Header section's Format event.

You never said how/where you calculate txtTotalHours and
txtDetailCount, so it may already be fine. Just to be sure,
both text boxes need to be in the group header:
txtTotalHours =Sum(Hours)
txtDetailCount =Count(*)

 

[toolsgg]

But I don't know the totals until the group footer? Also, it works if there
is more than one detail record as is.

In a report, I want to be able to supress the details section if the total
for the group is less than a certain value. If there are morte than one
detail record it works fine, but if there is just one detail record in the
group Access seems to ignore the code. I have debugged it and watched it
execute the code, but when you look at the report, the group is not
suppressed. Here is the code I am using in my group footer:
If Val([txtTotalHours]) > 0.166 Or Forms![Billing
Worksheet]![chkInclude10] = -1 Then
Me.Detail.Visible = True
If [txtDetailCount] > 1 Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
Else
Me.Detail.Visible = False
Me.GroupFooter0.Visible = False
End If
Anyone have any ideas how to make this work?

You have that code in the group Footer? But that's too late
to influance the details in the group. It should be in the
group Header section's Format event.

You never said how/where you calculate txtTotalHours and
txtDetailCount, so it may already be fine. Just to be sure,
both text boxes need to be in the group header:
txtTotalHours =Sum(Hours)
txtDetailCount =Count(*)

 

[Marshall Barton]

While Access reports bend over backwards in trying to make
things flexible for you, it is not very realistic to use
values that have not yet been calculated.

Why can't you calculate the values in the group header as I
suggested earlier?
--
Marsh
MVP [MS Access]

But I don't know the totals until the group footer? Also, it works if there
is more than one detail record as is.

In a report, I want to be able to supress the details section if the total
for the group is less than a certain value. If there are morte than one
detail record it works fine, but if there is just one detail record in the
group Access seems to ignore the code. I have debugged it and watched it
execute the code, but when you look at the report, the group is not
suppressed. Here is the code I am using in my group footer:
If Val([txtTotalHours]) > 0.166 Or Forms![Billing
Worksheet]![chkInclude10] = -1 Then
Me.Detail.Visible = True
If [txtDetailCount] > 1 Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
Else
Me.Detail.Visible = False
Me.GroupFooter0.Visible = False
End If
Anyone have any ideas how to make this work?

You have that code in the group Footer? But that's too late
to influance the details in the group. It should be in the
group Header section's Format event.

You never said how/where you calculate txtTotalHours and
txtDetailCount, so it may already be fine. Just to be sure,
both text boxes need to be in the group header:
txtTotalHours =Sum(Hours)
txtDetailCount =Count(*)

 

[toolsgg]

How do I calculate a group total in a group header? Won't it have the same
problem. It seems more realistic to have the value at the footer time than
at the header time. The weird thing is, that when I put the code in debug,
it has the correct values and flows through the code correctly. So, if the
value is less than the minimum I have set, it properly runs the code that
makes the detail line not visible, but then the report shows the line anyway.
I don't think the problem is with the totals, since they are correct, it is
with Access ignoring the commands to suppress the details and it only does it
if there is one line.

While Access reports bend over backwards in trying to make
things flexible for you, it is not very realistic to use
values that have not yet been calculated.

Why can't you calculate the values in the group header as I
suggested earlier?
--
Marsh
MVP [MS Access]

But I don't know the totals until the group footer? Also, it works if there
is more than one detail record as is.

toolsgg wrote:
In a report, I want to be able to supress the details section if the total
for the group is less than a certain value. If there are morte than one
detail record it works fine, but if there is just one detail record in the
group Access seems to ignore the code. I have debugged it and watched it
execute the code, but when you look at the report, the group is not
suppressed. Here is the code I am using in my group footer:
If Val([txtTotalHours]) > 0.166 Or Forms![Billing
Worksheet]![chkInclude10] = -1 Then
Me.Detail.Visible = True
If [txtDetailCount] > 1 Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
Else
Me.Detail.Visible = False
Me.GroupFooter0.Visible = False
End If
Anyone have any ideas how to make this work?

You have that code in the group Footer? But that's too late
to influance the details in the group. It should be in the
group Header section's Format event.

You never said how/where you calculate txtTotalHours and
txtDetailCount, so it may already be fine. Just to be sure,
both text boxes need to be in the group header:
txtTotalHours =Sum(Hours)
txtDetailCount =Count(*)

 

[Marshall Barton]

Note that the aggregate functions are calculated by an
internal query that Access creates to deal with Sorting and
Grouping as well as aggregations. This means that Count,
Sum, etc. can be placed in either the group (or report)
header or footer.

OTOH, VBA is dealing with the values of controls and will
refer to the value at the time the code executes. So
referencing a value in the footer will (usually??) get the
value from the previous group, which may or may not be the
same as the total of the current group.

Why not give my suggestion a try and see what happens?
--
Marsh
MVP [MS Access]

How do I calculate a group total in a group header? Won't it have the same
problem. It seems more realistic to have the value at the footer time than
at the header time. The weird thing is, that when I put the code in debug,
it has the correct values and flows through the code correctly. So, if the
value is less than the minimum I have set, it properly runs the code that
makes the detail line not visible, but then the report shows the line anyway.
I don't think the problem is with the totals, since they are correct, it is
with Access ignoring the commands to suppress the details and it only does it
if there is one line.

While Access reports bend over backwards in trying to make
things flexible for you, it is not very realistic to use
values that have not yet been calculated.

Why can't you calculate the values in the group header as I
suggested earlier?

But I don't know the totals until the group footer? Also, it works if there
is more than one detail record as is.


toolsgg wrote:
In a report, I want to be able to supress the details section if the total
for the group is less than a certain value. If there are morte than one
detail record it works fine, but if there is just one detail record in the
group Access seems to ignore the code. I have debugged it and watched it
execute the code, but when you look at the report, the group is not
suppressed. Here is the code I am using in my group footer:
If Val([txtTotalHours]) > 0.166 Or Forms![Billing
Worksheet]![chkInclude10] = -1 Then
Me.Detail.Visible = True
If [txtDetailCount] > 1 Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
Else
Me.Detail.Visible = False
Me.GroupFooter0.Visible = False
End If
Anyone have any ideas how to make this work?


:
You have that code in the group Footer? But that's too late
to influance the details in the group. It should be in the
group Header section's Format event.

You never said how/where you calculate txtTotalHours and
txtDetailCount, so it may already be fine. Just to be sure,
both text boxes need to be in the group header:
txtTotalHours =Sum(Hours)
txtDetailCount =Count(*)

 

[toolsgg]

Tried moving the code to the header and it did the same thing. So, if the
problem is that the vba code is referencing the previous groups control, how
do I get the total for the current group to us in my test?

Note that the aggregate functions are calculated by an
internal query that Access creates to deal with Sorting and
Grouping as well as aggregations. This means that Count,
Sum, etc. can be placed in either the group (or report)
header or footer.

OTOH, VBA is dealing with the values of controls and will
refer to the value at the time the code executes. So
referencing a value in the footer will (usually??) get the
value from the previous group, which may or may not be the
same as the total of the current group.

Why not give my suggestion a try and see what happens?
--
Marsh
MVP [MS Access]

How do I calculate a group total in a group header? Won't it have the same
problem. It seems more realistic to have the value at the footer time than
at the header time. The weird thing is, that when I put the code in debug,
it has the correct values and flows through the code correctly. So, if the
value is less than the minimum I have set, it properly runs the code that
makes the detail line not visible, but then the report shows the line anyway.
I don't think the problem is with the totals, since they are correct, it is
with Access ignoring the commands to suppress the details and it only does it
if there is one line.

While Access reports bend over backwards in trying to make
things flexible for you, it is not very realistic to use
values that have not yet been calculated.

Why can't you calculate the values in the group header as I
suggested earlier?


toolsgg wrote:
But I don't know the totals until the group footer? Also, it works if there
is more than one detail record as is.


toolsgg wrote:
In a report, I want to be able to supress the details section if the total
for the group is less than a certain value. If there are morte than one
detail record it works fine, but if there is just one detail record in the
group Access seems to ignore the code. I have debugged it and watched it
execute the code, but when you look at the report, the group is not
suppressed. Here is the code I am using in my group footer:
If Val([txtTotalHours]) > 0.166 Or Forms![Billing
Worksheet]![chkInclude10] = -1 Then
Me.Detail.Visible = True
If [txtDetailCount] > 1 Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
Else
Me.Detail.Visible = False
Me.GroupFooter0.Visible = False
End If
Anyone have any ideas how to make this work?


:
You have that code in the group Footer? But that's too late
to influance the details in the group. It should be in the
group Header section's Format event.

You never said how/where you calculate txtTotalHours and
txtDetailCount, so it may already be fine. Just to be sure,
both text boxes need to be in the group header:
txtTotalHours =Sum(Hours)
txtDetailCount =Count(*)

 

[Marshall Barton]

Did you also move the calculated text boxes to the group
header section as I originally suggested?

This is not a particularly complicated thing to do and I
have never seen a bug with this kind of thing. You just
have to get the code and the calculated controls properly
synchronized.
--
Marsh
MVP [MS Access]

Tried moving the code to the header and it did the same thing. So, if the
problem is that the vba code is referencing the previous groups control, how
do I get the total for the current group to us in my test?

Note that the aggregate functions are calculated by an
internal query that Access creates to deal with Sorting and
Grouping as well as aggregations. This means that Count,
Sum, etc. can be placed in either the group (or report)
header or footer.

OTOH, VBA is dealing with the values of controls and will
refer to the value at the time the code executes. So
referencing a value in the footer will (usually??) get the
value from the previous group, which may or may not be the
same as the total of the current group.

Why not give my suggestion a try and see what happens?

How do I calculate a group total in a group header? Won't it have the same
problem. It seems more realistic to have the value at the footer time than
at the header time. The weird thing is, that when I put the code in debug,
it has the correct values and flows through the code correctly. So, if the
value is less than the minimum I have set, it properly runs the code that
makes the detail line not visible, but then the report shows the line anyway.
I don't think the problem is with the totals, since they are correct, it is
with Access ignoring the commands to suppress the details and it only does it
if there is one line.


:
While Access reports bend over backwards in trying to make
things flexible for you, it is not very realistic to use
values that have not yet been calculated.

Why can't you calculate the values in the group header as I
suggested earlier?


toolsgg wrote:
But I don't know the totals until the group footer? Also, it works if there
is more than one detail record as is.


toolsgg wrote:
In a report, I want to be able to supress the details section if the total
for the group is less than a certain value. If there are morte than one
detail record it works fine, but if there is just one detail record in the
group Access seems to ignore the code. I have debugged it and watched it
execute the code, but when you look at the report, the group is not
suppressed. Here is the code I am using in my group footer:
If Val([txtTotalHours]) > 0.166 Or Forms![Billing
Worksheet]![chkInclude10] = -1 Then
Me.Detail.Visible = True
If [txtDetailCount] > 1 Then
Me.GroupFooter0.Visible = True
Else
Me.GroupFooter0.Visible = False
End If
Else
Me.Detail.Visible = False
Me.GroupFooter0.Visible = False
End If
Anyone have any ideas how to make this work?


:
You have that code in the group Footer? But that's too late
to influance the details in the group. It should be in the
group Header section's Format event.

You never said how/where you calculate txtTotalHours and
txtDetailCount, so it may already be fine. Just to be sure,
both text boxes need to be in the group header:
txtTotalHours =Sum(Hours)
txtDetailCount =Count(*)