By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 775 Online
Bytes IT Community
+ Ask a Question
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

kcdoell
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
  1. =Sum([GWP])
  2.  
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
  1. =sum(iif((Binding_Percentage)=50%))
  2.  
but I keep on getting complile errors. I think because the value list is text.

Can anybody assist?

Thanks
Mar 25 '08 #1
Share this Question
Share on Google+
18 Replies


ADezii
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
  1. =Sum([GWP])
  2.  
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
  1. =sum(iif((Binding_Percentage)=50%))
  2.  
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
  1. =DSum("[GWP]","Table1","Val([Binding_Percentage]) >= 50")
Mar 25 '08 #2

kcdoell
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
  1. =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

kcdoell
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
  1. =DSum("[GWP]","ReQryForecast","Val([Binding_Percentage]) >= 50")
  2.  
Mar 25 '08 #4

kcdoell
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

ADezii
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

kcdoell
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
  1. =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
  1. =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

ADezii
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
  1. =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
  1. =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
  1. =IIf(Val([Binding_Percentage]) < 75, 0, DSum([GWP], "ReQryForecast"))
Apr 29 '08 #8

kcdoell
100+
P: 230
Your great, worked perfectly......

Thanks,

Keith.
Apr 29 '08 #9

ADezii
Expert 5K+
P: 8,638
Your great, worked perfectly......

Thanks,

Keith.
You are quite welcome, Keith.
Apr 29 '08 #10

kcdoell
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

ADezii
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
  1. =IIf(Val([Binding_Percentage]) < 75, 0, DSum("[GWP]", "ReQryForecast", "[Binding_Percentage] > = 75"))
Apr 30 '08 #12

kcdoell
100+
P: 230
Try
Expand|Select|Wrap|Line Numbers
  1. =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

ADezii
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

kcdoell
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

kcdoell
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

kcdoell
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

ADezii
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

kcdoell
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

Post your reply

Sign in to post your reply or Sign up for a free account.