Sum detail list without invisible list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I made a data list, and some data may miss 1 or 2 sections. I code it to
change its visible in the report. The item is insivible in the report page.
However, when I sum its value. The sum function still add the insivible
value. How do I avoid this?

lkhsu

----------------
1000
2000 <-(this line is invisible, but now sum still add it, how to make sum
bypass it)
3000
4000
 
The formula that you are using in the report that decide which section is
invisible, use it in the sum for the field.
e.g

=Sum(iif(criteria=true,FieldName,0))
 
I tried to use =Sum(IIf([sample].[Visible]=true,FieldName,0))
but when it runs, it will popup a window and ask for "Enter Parameter
Value---sample.Visible". How to fix it? Thank you.

lkhsu
 
The criteria should be the same criteria you use to define when the sample is
visible and when its not
--
I hope that helped
Good luck


fox said:
I tried to use =Sum(IIf([sample].[Visible]=true,FieldName,0))
but when it runs, it will popup a window and ask for "Enter Parameter
Value---sample.Visible". How to fix it? Thank you.

lkhsu

Ofer said:
The formula that you are using in the report that decide which section is
invisible, use it in the sum for the field.
e.g

=Sum(iif(criteria=true,FieldName,0))
Click to expand...
Click to expand...
 
Would you mind sharing how you "code it to change its visible". Your answer
may be in your response somewhere.

--
Duane Hookom
MS Access MVP


fox said:
I tried to use =Sum(IIf([sample].[Visible]=true,FieldName,0))
but when it runs, it will popup a window and ask for "Enter Parameter
Value---sample.Visible". How to fix it? Thank you.

lkhsu

Ofer said:
The formula that you are using in the report that decide which section is
invisible, use it in the sum for the field.
e.g

=Sum(iif(criteria=true,FieldName,0))
Click to expand...
Click to expand...
 
The report looks like this
Sample SampleValue Test1 Test2 Total
1 1000 10 20 30
2 2000
3 3000 10 20 30
4 4000 10 20 30

PS. Total = Test1 + Test2
and 3 values are calculated when it displayed.

Here is my VBA code to hide the line
If Val(Format(Me.Total.Value, "#.#")) <> 0 Then
Me.Sample.Visible = True
Me.SampleValue.Visible = True
Else
Me.Sample.Visible = False
Me.SampleValue.Visible = False
End If

If I use Total as criteria, it will ask me to enter the value of Total, too.
=Sum(iif(criteria=true,FieldName,0))

Thank you for help.

lkhsu

Duane Hookom said:
Would you mind sharing how you "code it to change its visible". Your answer
may be in your response somewhere.

--
Duane Hookom
MS Access MVP


fox said:
I tried to use =Sum(IIf([sample].[Visible]=true,FieldName,0))
but when it runs, it will popup a window and ask for "Enter Parameter
Value---sample.Visible". How to fix it? Thank you.

lkhsu

Ofer said:
The formula that you are using in the report that decide which section is
invisible, use it in the sum for the field.
e.g

=Sum(iif(criteria=true,FieldName,0))
Click to expand...
Click to expand...
Click to expand...
 
Try this
=Sum(iif([Test1]+[Test2]=0,0,[SampleValue]))
--
I hope that helped
Good luck


fox said:
The report looks like this
Sample SampleValue Test1 Test2 Total
1 1000 10 20 30
2 2000
3 3000 10 20 30
4 4000 10 20 30

PS. Total = Test1 + Test2
and 3 values are calculated when it displayed.

Here is my VBA code to hide the line
If Val(Format(Me.Total.Value, "#.#")) <> 0 Then
Me.Sample.Visible = True
Me.SampleValue.Visible = True
Else
Me.Sample.Visible = False
Me.SampleValue.Visible = False
End If

If I use Total as criteria, it will ask me to enter the value of Total, too.
=Sum(iif(criteria=true,FieldName,0))

Thank you for help.

lkhsu

Duane Hookom said:
Would you mind sharing how you "code it to change its visible". Your answer
may be in your response somewhere.

--
Duane Hookom
MS Access MVP


fox said:
I tried to use =Sum(IIf([sample].[Visible]=true,FieldName,0))
but when it runs, it will popup a window and ask for "Enter Parameter
Value---sample.Visible". How to fix it? Thank you.

lkhsu

:

The formula that you are using in the report that decide which section is
invisible, use it in the sum for the field.
e.g

=Sum(iif(criteria=true,FieldName,0))
Click to expand...
Click to expand...
Click to expand...
 
Yes, this works. Thank you very much.
So, Could you tell me what the reason is I can't use Total as the key?

lkhsu

Ofer said:
Try this
=Sum(iif([Test1]+[Test2]=0,0,[SampleValue]))
--
I hope that helped
Good luck


fox said:
The report looks like this
Sample SampleValue Test1 Test2 Total
1 1000 10 20 30
2 2000
3 3000 10 20 30
4 4000 10 20 30

PS. Total = Test1 + Test2
and 3 values are calculated when it displayed.

Here is my VBA code to hide the line
If Val(Format(Me.Total.Value, "#.#")) <> 0 Then
Me.Sample.Visible = True
Me.SampleValue.Visible = True
Else
Me.Sample.Visible = False
Me.SampleValue.Visible = False
End If

If I use Total as criteria, it will ask me to enter the value of Total, too.
=Sum(iif(criteria=true,FieldName,0))

Thank you for help.

lkhsu

Duane Hookom said:
Would you mind sharing how you "code it to change its visible". Your answer
may be in your response somewhere.

--
Duane Hookom
MS Access MVP


I tried to use =Sum(IIf([sample].[Visible]=true,FieldName,0))
but when it runs, it will popup a window and ask for "Enter Parameter
Value---sample.Visible". How to fix it? Thank you.

lkhsu

:

The formula that you are using in the report that decide which section is
invisible, use it in the sum for the field.
e.g

=Sum(iif(criteria=true,FieldName,0))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top