435,241 Members | 775 Online
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

# Summing a number based on a text field

 100+ P: 230 Hello: I have two fields on a table that is displayed on my continous form. GWP = Number Binding Percentage = Text (a value list of 25%, 50%, 90% & 100%) I have a unbound text box that I placed in the footer and in the control source I wrote the following: Expand|Select|Wrap|Line Numbers =Sum([GWP])   This works but now I am trying to write this code to only sum the "GWP" where the value list of my binding percentage field is 50% or greater. I tried this: Expand|Select|Wrap|Line Numbers =sum(iif((Binding_Percentage)=50%))   but I keep on getting complile errors. I think because the value list is text. Can anybody assist? Thanks Mar 25 '08 #1
18 Replies

 Expert 5K+ P: 8,638 Hello: I have two fields on a table that is displayed on my continous form. GWP = Number Binding Percentage = Text (a value list of 25%, 50%, 90% & 100%) I have a unbound text box that I placed in the footer and in the control source I wrote the following: Expand|Select|Wrap|Line Numbers =Sum([GWP])   This works but now I am trying to write this code to only sum the "GWP" where the value list of my binding percentage field is 50% or greater. I tried this: Expand|Select|Wrap|Line Numbers =sum(iif((Binding_Percentage)=50%))   but I keep on getting complile errors. I think because the value list is text. Can anybody assist? Thanks Assuming your Table Name is Table1, and you are including 'ALL' the Records from Table1 in the Continuous Form: Expand|Select|Wrap|Line Numbers =DSum("[GWP]","Table1","Val([Binding_Percentage]) >= 50") Mar 25 '08 #2

 100+ P: 230 Assuming your Table Name is Table1, and you are including 'ALL' the Records from Table1 in the Continuous Form: Expand|Select|Wrap|Line Numbers =DSum("[GWP]","Table1","Val([Binding_Percentage]) >= 50") Actually my continous form's record source is driven by a query called "ReQryForecast" Will that make a difference ADezii? Mar 25 '08 #3

 100+ P: 230 Actually my continous form's record source is driven by a query called "ReQryForecast" Will that make a difference ADezii? Figured it out, Thanks!! Expand|Select|Wrap|Line Numbers =DSum("[GWP]","ReQryForecast","Val([Binding_Percentage]) >= 50")   Mar 25 '08 #4

 100+ P: 230 Thanks for all the help I was reading about the VAL method last night and will use it in the future. Best regards, Keith. Mar 25 '08 #5

 Expert 5K+ P: 8,638 Thanks for all the help I was reading about the VAL method last night and will use it in the future. Best regards, Keith. You are quite welcome, Keith. Mar 25 '08 #6

 100+ P: 230 You are quite welcome, Keith. ADezii: Hello again, I have one more situation regarding this subject. Currently I have the text box's control source, SumGWP, set to: Expand|Select|Wrap|Line Numbers =DSum("[GWP]","ReQryForecast","Val([Binding_Percentage]) >= 75") if "Binding_Percentage" is < 75 than my unbound text box "SumGWP" is blank even when I set its default value property to 0. I thought I would apply the Nz expression below: Expand|Select|Wrap|Line Numbers =DSum(Nz([GWP],0),"ReQryForecast","Val([Binding_Percentage]) >= 75") But that did not work either. How would I best approach this scenario so that it will populate a 0 instead of a blank if "Binding_Percentage" is < 75? Thanks and hope all is well, Best regards, Keith. Apr 29 '08 #7

 Expert 5K+ P: 8,638 ADezii: Hello again, I have one more situation regarding this subject. Currently I have the text box's control source, SumGWP, set to: Expand|Select|Wrap|Line Numbers =DSum("[GWP]","ReQryForecast","Val([Binding_Percentage]) >= 75") if "Binding_Percentage" is < 75 than my unbound text box "SumGWP" is blank even when I set its default value property to 0. I thought I would apply the Nz expression below: Expand|Select|Wrap|Line Numbers =DSum(Nz([GWP],0),"ReQryForecast","Val([Binding_Percentage]) >= 75") But that did not work either. How would I best approach this scenario so that it will populate a 0 instead of a blank if "Binding_Percentage" is < 75? Thanks and hope all is well, Best regards, Keith. Try: Expand|Select|Wrap|Line Numbers =IIf(Val([Binding_Percentage]) < 75, 0, DSum([GWP], "ReQryForecast")) Apr 29 '08 #8

 100+ P: 230 Your great, worked perfectly...... Thanks, Keith. Apr 29 '08 #9

 Expert 5K+ P: 8,638 Your great, worked perfectly...... Thanks, Keith. You are quite welcome, Keith. Apr 29 '08 #10

 100+ P: 230 Sorry Adizzii: I thought I was done with this but it is now given me the 0 when less than 75 but not doing anthing when it is >=75..... Any ideas? Thanks Keith. Apr 30 '08 #11

 Expert 5K+ P: 8,638 Sorry Adizzii: I thought I was done with this but it is now given me the 0 when less than 75 but not doing anthing when it is >=75..... Any ideas? Thanks Keith. Try Expand|Select|Wrap|Line Numbers =IIf(Val([Binding_Percentage]) < 75, 0, DSum("[GWP]", "ReQryForecast", "[Binding_Percentage] > = 75")) Apr 30 '08 #12

 100+ P: 230 Try Expand|Select|Wrap|Line Numbers =IIf(Val([Binding_Percentage]) < 75, 0, DSum("[GWP]", "ReQryForecast", "[Binding_Percentage] > = 75")) ADezii: Once I selected 75 or greater I received a #Error.....in the unbound text box. It did still place a "0" when I selected something less than 75. I have read so much on Nz and Null values but still find it very confusing. Your thoughts? Thanks, Keith. May 2 '08 #13

 Expert 5K+ P: 8,638 ADezii: Once I selected 75 or greater I received a #Error.....in the unbound text box. It did still place a "0" when I selected something less than 75. I have read so much on Nz and Null values but still find it very confusing. Your thoughts? Thanks, Keith. Does the [Binding_Percentage] Field contain strictly Numeric values, namely: 50, 60, 75, 80, etc...? May 2 '08 #14

 100+ P: 230 Does the [Binding_Percentage] Field contain strictly Numeric values, namely: 50, 60, 75, 80, etc...? ADezii: [Binding_Percentage] is a combo box whose row source type is set to a value list. I have the row source set to: "0%";"50%";"75%";"100%" I used the "Val" because [Binding_Percentage] is a text box. Thanks, Keith. May 5 '08 #15

 100+ P: 230 ADezii: [Binding_Percentage] is a combo box whose row source type is set to a value list. I have the row source set to: "0%";"50%";"75%";"100%" I used the "Val" because [Binding_Percentage] is a text box. Thanks, Keith. You have got me thinking. I changed [Binding_Percentage] to a number and set the row source set to: 0;50;75;100 Let me see if that changed anything, Thanks, Keith. May 5 '08 #16

 100+ P: 230 That solved the problems and also eliminated other issues I was having except one.... I will open another thread to address that one. Thanks! Keith. May 5 '08 #17

 Expert 5K+ P: 8,638 That solved the problems and also eliminated other issues I was having except one.... I will open another thread to address that one. Thanks! Keith. Anytime Keith, but you basically solved it yourself! (LOL). May 5 '08 #18

 100+ P: 230 Yes, and that led me to solve another issue by myself: Dividing by zero... Wow, I am on a roll. Now it is back to my other problem that has been eating at me for over a week: Creating a pivot table looking report…….. Any ideas would be helpfull. Hope all is well, Keith. :-) May 5 '08 #19