"#Error" in an Unbound Text box - Continuous form

[kcdoell]

Hello:

I three fields on a continuous form:
[GWP], [NWP] & [Binding_Percent age]

On the same form I have an unbound text box [Sum50NWP] with the following formula in the control source:

 =Sum(IIf([Binding_Percentage]=50,[NWP],0)) 

This worked great.

Now I am being requested that my user does not want an End User to input a number into the [NWP] field but rather have it be calculated on the form. You see, it has been brought to light that [NWP] is equal to [GWP] – [Ceded] ([Ceded] would be a new field for my table). Since I have heard that you never want to store a value that is dependent on other fields, I eliminated my [NWP] field from my table added [Ceded] and then in the form I added an unbound text box called [NWP_Calc]. In the control source I have the following Vb:

 =[tblAllForecast!GWP]-[tblAllForecast!Ceded] 

This works great. I modified my [Sum50NWP] with the following formula in the control source:

 =Sum(IIf([Binding_Percentage]=50,[Forms!Forecast!NWP_Calc],0)) 

but now I am getting a “#Error” in my [Sum50NWP].

Any ideas what I missed?

Thanks,

Keith.

[kcdoell]

Hello Me again:

A further note, that I don’t fully understand, is that my other unbound text box [Sum50GWP] with the following formula in the control source:

 =Sum(IIf([Binding_Percentage]=50,[GWP],0)) 

Is also getting this same error message “#Error”. Is the fact that since now I am basing my control source of [Sum50GWP] on another calculated unbound control [NWP_Calc] the root of the problem?

Best Regards,

Keith.

[Megalog]

My guess would be to try bracketing each object separately like this:

=Sum(IIf([Binding_Percentage]=50,[Forms]![Forecast]![NWP_Calc],0))

If it isnt a syntax problem, then maybe it's a format issue.. like NWP_Calc might be text when it needs to be converted to a long number for the Sum.

Or, could be it doesnt like the unbound data with the continuous form.

[kcdoell]

Hello:

I checked the properties on [NWP_Calc] and it is set to Standard. This is in sync with my [GWP] & [Cede] controls so that should not be it.

I also tried the new syntax but get the same result. Your third idea may make more sense because I indicated that my other unbound text box [Sum50GWP] is impacted as well where I would have thought otherwise since the control source it is not dependent on [NWP_Calc]. If the later is true, what other method could I try?

Thanks.

[kcdoell]

I have also taken another route with the same result ("#Error"). My form is based on a query, so I added a calculated field in the query [NWP_Calc2] and then when back to the form, went to "field list" and dropped the control into the form. The new control calculates correctly. Then I modified my [Sum50NWP] with the following formula in the control source:

 =Sum(IIf([Binding_Percentage]=50,[NWP_Calc2],0)) 

I still get the same error in [Sum50NWP]

Any ideas? It worked before when I did not enter this new logic.

Thanks,

Keith.

[missinglinq]

If I remember correctly, you can't include a calculated field in a Sum() function, but rather you have to replace the calculated field with the expression that populates the field.

Linq ;0)>

[kcdoell]

I have also taken another route with the same result ("#Error"). My form is based on a query, so I added a calculated field in the query [NWP_Calc2] and then when back to the form, went to "field list" and dropped the control into the form. The new control calculates correctly. Then I modified my [Sum50NWP] with........... .
Keith.

Okay:

I was playing around with this and stuck with my above query idea. I changed the control name from [NWP_Calc2] to [NWP_Calc] and added the following code to my control [Sum50NWP]:

 =Sum(IIf([Binding_Percentage]=50,Nz([NWP_Calc],0),0)) 

Checked my other vb on this form, performed a "Compact and Repair Database...." and bang it worked!

Thanks for the ideas.

Keith.