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
+ 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

 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  =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  =[tblAllForecast!GWP]-[tblAllForecast!Ceded]  This works great. I modified my [Sum50NWP] with the following formula in the control source: Expand|Select|Wrap|Line Numbers  =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
6 Replies

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

 Expert 100+ P: 378 My guess would be to try bracketing each object separately like this: Expand|Select|Wrap|Line Numbers =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

 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

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

 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

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