?
Solved

Microsoft Access Control Source Error

Posted on 2012-08-27
13
Medium Priority
?
629 Views
Last Modified: 2012-08-31
Hello, I have a report in access 2010 that shows separate percentages for the three expressions below built into separate control sources.  The only difference in these expressions is the “bkfst”, “lun” and “din”.
However, the breakfast gives me a “#error” reading on the report while the Lunch and Dinner give me the correct result of percentages.  I think it might have something to do with the number of characters in the expression being more than 300.  The Lunch and dinner have 285 characters while the breakfast has 317.  When I remove the last field (i.e. +[ps1_bkfst_coffee]) from the breakfast expression the character count is 289.  When I add one more field (i.e. +[ps1_lun_coffee] or +[ps1_din_coffee]) to the end of the lunch or dinner expression the character count is 303 and I get the same “#error” error as with breakfast.
I can’t determine any other reason for breakfast not providing the percentage.  If there is a restriction on the number of characters in a control source, how do I get around it?  Thank you.

ic

Breakfast:

=Sum(IIf(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])<>0,(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])-([ps13_bkfst_food]+[ps13_bkfst_wine]+[ps13_bkfst_liquor]+[ps13_bkfst_coffee]))/([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee]),0))

Lunch:

=Sum(IIf(([ps1_lun_food]+[ps1_lun_wine]+[ps1_lun_liquor]+[ps1_lun_coffee])<>0,(([ps1_lun_food]+[ps1_lun_wine]+[ps1_lun_liquor]+[ps1_lun_coffee])-([ps13_lun_food]+[ps13_lun_wine]+[ps13_lun_liquor]+[ps13_lun_coffee]))/([ps1_lun_food]+[ps1_lun_wine]+[ps1_lun_liquor]+[ps1_lun_coffee]),0))

Dinner:

=Sum(IIf(([ps1_din_food]+[ps1_din_wine]+[ps1_din_liquor]+[ps1_din_coffee])<>0,(([ps1_din_food]+[ps1_din_wine]+[ps1_din_liquor]+[ps1_din_coffee])-([ps13_din_food]+[ps13_din_wine]+[ps13_din_liquor]+[ps13_din_coffee]))/([ps1_din_food]+[ps1_din_wine]+[ps1_din_liquor]+[ps1_din_coffee]),0))
the-report.doc
0
Comment
Question by:ilfocorp
  • 6
  • 4
  • 3
13 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38339119
I'm thinking that you may have a null field for one of your records for ps1_bkfst_coffee.  Try using the Nz function.  
+Nz([ps1_bkfst_coffee],0)
0
 
LVL 75
ID: 38339120
To cover for 'Null propagation" (values that may be null) ... try wrapping each item with Nz()


Nz([ps1_bkfst_food],0) + Nz([ps1_bkfst_wine],0) + Nz([ps1_bkfst_liquor],0) ... and so on.

Do this for all cases (B/L/D) ...

mx
0
 

Author Comment

by:ilfocorp
ID: 38341850
Thanks for the response.  I changed the breakfast from the first to the second and continue to get the #error on the report page.  However, I didn't get any syntax error.  Does this syntax look right?

First:
=Sum(IIf(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])<>0,(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])-([ps13_bkfst_food]+[ps13_bkfst_wine]+[ps13_bkfst_liquor]+[ps13_bkfst_coffee]))/([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee]),0))

Second:

=Sum(IIf((Nz([ps1_bkfst_food],0)+Nz([ps1_bkfst_wine],0)+Nz([ps1_bkfst_liquor],0)+Nz([ps1_bkfst_coffee],0))<>0,
((Nz([ps1_bkfst_food],0)+Nz([ps1_bkfst_wine],0)+Nz([ps1_bkfst_liquor],0)+Nz([ps1_bkfst_coffee],0))-(Nz([ps13_bkfst_food],0)+
Nz([ps13_bkfst_wine],0)+Nz([ps13_bkfst_liquor],0)+Nz([ps13_bkfst_coffee],0)))/
(Nz([ps1_bkfst_food],0)+Nz([ps1_bkfst_wine],0)+Nz([ps1_bkfst_liquor],0)+Nz([ps1_bkfst_coffee],0)),0))
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 525 total points
ID: 38342229
#Error occurs when Access cannot resolve something in the expression, typically a typo in a field name, etc., or the Null propagation error,  or some other issue with the expression, like an extra or missing bracket or paren, etc.

Double check all of this ...
I am not able to compile this expression in VBA code:

1
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38343808
You also need to make sure that [ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee] doesn't equal 0 or you would be dividing by zero which would throw an error.

One way to find the culprit is to remove fields from your equation one at a time until you don't have the error.  Then figure out what's wrong with the offending field.
0
 

Author Comment

by:ilfocorp
ID: 38346165
Thanks. one thing I did was, since the expressions are the same for all three food categories, I replaced bkfst in the breakfast expression with lun and copied the breakfast expression over the lunch and the lunch worked fine.  So it's not the breakfast expression in and of itself.  As IrogSinta suggested it may have to do with one of the fields.  However, I did remove random fields from the breakfast and it worked.  I will removed them one at a time to see if I get the error at any given point.

ic
0
 

Author Comment

by:ilfocorp
ID: 38346317
Ok, I ran the report with each field removed one at a time from the breakfast expression and got a percentage every time (no #error).  

Again, since the expressions are the same for all three food categories, I replaced bkfst in the breakfast expression with lun and copied the breakfast expression over the lunch and the lunch worked fine.

Again, the Nz gave me the same #error as well.

As stated earlier, the only time that I encounter this #error any one of the food categories is when the expression exceeds 300 characters.  It sounds a bit silly but I do know there is a restriction on the amount of script in a module.  Could it be conceivable that there is a restriction on control sources?

Thanks again.
0
 
LVL 75
ID: 38346542
I can't say I've ever heard of a specific restriction, and if there is, I'm pretty sure it would be more like 2048 or something ...

Did you check for divide by zero as suggested above?

mx
0
 

Author Comment

by:ilfocorp
ID: 38348271
I now summed the two equations and divided the difference and found no zero and got 0.156.

=Sum(IIf(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])<>0,(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])-([ps13_bkfst_food]+[ps13_bkfst_wine]+[ps13_bkfst_liquor]+[ps13_bkfst_coffee]))/([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee]),0))

=Sum([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])
$15.62

=Sum([ps13_bkfst_food]+[ps13_bkfst_wine]+[ps13_bkfst_liquor]+[ps13_bkfst_coffee])
$13.18

15.62-13.18=2.44
2.44/15.62=0.156

Any other suggestions.  Thanks for all the support.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38348305
Are you able to upload a sample database with this report and the table?
0
 

Author Comment

by:ilfocorp
ID: 38348347
Here is a sample of db.

Thank you.
ChkAvg-Sample.accdb
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 525 total points
ID: 38348657
Apparently, you're right about the 300 character limit in a textbox calculation.  You may have to add an invisible textbox to store part of your calculation and use the name of that textbox in your calculation instead.

Having said that.  You really need to think about changing how your table is set up.  This table is not at all normalized and would be difficult to work with in queries, forms, and reports.  A better way to would be to have a date field (where you can extract your month and year),  along with your el2_Name field, a Mealtime field (to identify Breakfast, Lunch, or Dinner), a Category field (for Food, Wine, Liquor, or Coffee), and a field for Cost.  

Using a CrossTab query, you can create a report just like the one you have but without all those individual calculations.
0
 

Author Comment

by:ilfocorp
ID: 38355974
You are absolutely correct about the normalization of the tables.  I was able however to figure out a work around.

SOLUTION:
Replace this original expression

=Sum(IIf(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])<>0,(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])-([ps13_bkfst_food]+[ps13_bkfst_wine]+[ps13_bkfst_liquor]+[ps13_bkfst_coffee]))/([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee]),0))

With this expression (removing the Sum, the first open parenthesis and last closed parenthesis).

=IIf(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])<>0,(([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee])-([ps13_bkfst_food]+[ps13_bkfst_wine]+[ps13_bkfst_liquor]+[ps13_bkfst_coffee]))/([ps1_bkfst_food]+[ps1_bkfst_wine]+[ps1_bkfst_liquor]+[ps1_bkfst_coffee]),0)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question