Sum detail list without invisible list

F

fox

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
 
O

Ofer

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))
 
F

fox

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
 
O

Ofer

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...
 
D

Duane Hookom

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...
 
F

fox

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...
 
O

Ofer

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...
 
F

fox

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...
 
Top