Summing a number based on a text field

[kcdoell]

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:

=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:

=sum(iif((Binding_Percentage)=50%))
 

but I keep on getting complile errors. I think because the value list is text.

Can anybody assist?

Thanks

[ADezii]

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:

=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:

=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:

=DSum("[GWP]","Table1","Val([Binding_Percentage]) >= 50")

[kcdoell]

Assuming your Table Name is Table1, and you are including 'ALL' the Records from Table1 in the Continuous Form:

=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?

[kcdoell]

Actually my continous form's record source is driven by a query called "ReQryForecast" Will that make a difference ADezii?

Figured it out, Thanks!!

=DSum("[GWP]","ReQryForecast","Val([Binding_Percentage]) >= 50")
 

[kcdoell]

Thanks for all the help I was reading about the VAL method last night and will use it in the future.

Best regards,

Keith.

[ADezii]

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.

[kcdoell]

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:

=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:

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

[ADezii]

ADezii:

Hello again, I have one more situation regarding this subject. Currently I have the text box's control source, SumGWP, set to:

=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:

=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:

=IIf(Val([Binding_Percentage]) < 75, 0, DSum([GWP], "ReQryForecast"))

[kcdoell]

Your great, worked perfectly......

Thanks,

Keith.

[ADezii]

Your great, worked perfectly......

Thanks,

Keith.

You are quite welcome, Keith.

[kcdoell]

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.

[ADezii]

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

=IIf(Val([Binding_Percentage]) < 75, 0, DSum("[GWP]", "ReQryForecast", "[Binding_Percentage] > = 75"))

[kcdoell]

Try

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

[ADezii]

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

[kcdoell]

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.

[kcdoell]

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.

[kcdoell]

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.

[ADezii]

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

[kcdoell]

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. :-)