|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 5th, 2005, 05:36 PM
|
|||
|
|||
Sum detail list without invisible list
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 --------- Sum=10000 
|
|
#2
October 5th, 2005, 06:20 PM
|
|||
|
|||
|
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 hope that helped Good luck "fox" wrote: 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 --------- Sum=10000
|
|
#3
October 5th, 2005, 07:27 PM
|
|||
|
|||
|
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" wrote: 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 hope that helped Good luck "fox" wrote: 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 --------- Sum=10000
|
|
#4
October 5th, 2005, 07:36 PM
|
|||
|
|||
|
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" wrote: 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" wrote: 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 hope that helped Good luck "fox" wrote: 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 --------- Sum=10000
|
|
#5
October 5th, 2005, 07:38 PM
|
|||
|
|||
|
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" wrote in message ... 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" wrote: 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 hope that helped Good luck "fox" wrote: 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 --------- Sum=10000
|
|
#6
October 5th, 2005, 08:44 PM
|
|||
|
|||
|
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" wrote: 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" wrote in message ... 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" wrote: 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 hope that helped Good luck
|
|
#7
October 5th, 2005, 10:09 PM
|
|||
|
|||
|
Try this
=Sum(iif([Test1]+[Test2]=0,0,[SampleValue])) -- I hope that helped Good luck "fox" wrote: 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" wrote: 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" wrote in message ... 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" wrote: 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 hope that helped Good luck
|
|
#8
October 6th, 2005, 05:36 PM
|
|||
|
|||
|
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" wrote: Try this =Sum(iif([Test1]+[Test2]=0,0,[SampleValue])) -- I hope that helped Good luck "fox" wrote: 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" wrote: 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" wrote in message ... 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" wrote: 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 hope that helped Good luck
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| SORTING BY PERCENTAGE | jeanne | Running & Setting Up Queries | 6 | January 19th, 2005 09:33 PM |
| Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
| Clear the "Recent Files" list in the hyperlink window | Mike | Powerpoint | 15 | July 22nd, 2004 02:51 AM |
| outlook 2003 public folders of custom list | Lynda | Contacts | 1 | June 9th, 2004 01:25 PM |
| How to make list of unique values? | JulieD | Worksheet Functions | 1 | February 26th, 2004 12:25 PM |
Linear Mode
