By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 799 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.

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

kcdoell
100+
P: 230
Hello:

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

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

Expand|Select|Wrap|Line Numbers
  1.  =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:

Expand|Select|Wrap|Line Numbers
  1.  =[tblAllForecast!GWP]-[tblAllForecast!Ceded] 
This works great. I modified my [Sum50NWP] with the following formula in the control source:
Expand|Select|Wrap|Line Numbers
  1.  =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.
Jun 11 '08 #1
Share this Question
Share on Google+
6 Replies


kcdoell
100+
P: 230
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:

Expand|Select|Wrap|Line Numbers
  1.  =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.
Jun 11 '08 #2

Megalog
Expert 100+
P: 378
My guess would be to try bracketing each object separately like this:

Expand|Select|Wrap|Line Numbers
  1. =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.
Jun 11 '08 #3

kcdoell
100+
P: 230
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.
Jun 11 '08 #4

kcdoell
100+
P: 230
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:

Expand|Select|Wrap|Line Numbers
  1.  =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.
Jun 11 '08 #5

missinglinq
Expert 2.5K+
P: 3,532
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)>
Jun 11 '08 #6

kcdoell
100+
P: 230
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]:

Expand|Select|Wrap|Line Numbers
  1.  =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.
Jun 12 '08 #7

Post your reply

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