Code Error - Can you have too many expressions?

**aaron.irvine** · 10-28-2013, 09:11 PM

Hey guys,

This one has completely stumped me! I've got a few sets of code to give me average results and percentages of a number of categories. Two sets of code, which work, are combined to give overall results - but I cannot get the code to work! It results in an #ERROR display.

Here are the average & percentage expressions that work fine:

Average of Signatures recorded across multiple days of multiple sheets

Code:

=Round(Abs(Avg([QCflavourChangeSig]+[QCfillerOperatorSig]+[QCblowMouldingSig]+[QCtorqueTestSig]+[QCnetContentsSig]+[QClabellerSig]+[QCpackerSig]+[QCpalletiserSig]+[RMpreformSig]+[RMclosureSig]+[RMlabelSig]+[RMcartonSig])),2)

Percentage of Signatures recorded across multiple days of multiple sheets

Code:

=Abs(Avg([QCflavourChangeSig]+[QCfillerOperatorSig]+[QCblowMouldingSig]+[QCtorqueTestSig]+[QCnetContentsSig]+[QClabellerSig]+[QCpackerSig]+[QCpalletiserSig]+[RMpreformSig]+[RMclosureSig]+[RMlabelSig]+[RMcartonSig]))/12

Total number of Signatures and Tests completed successfully across one day of multiple sheets

Code:

=Abs([QCflavourChangeSig]+[QCfillerOperatorSig]+[QCblowMouldingSig]+[QCtorqueTestSig]+[QCnetContentsSig]+[QClabellerSig]+[QCpackerSig]+[QCpalletiserSig]+[RMpreformSig]+[RMclosureSig]+[RMlabelSig]+[RMcartonSig]+[QCflavourChange]+[QCfillerOperator]+[QCblowMoulding]+[QCtorqueTest]+[QCnetContents]+[QClabeller]+[QCpacker]+[QCpalletiser]+[RMpreform]+[RMclosure]+[RMlabel]+[RMcarton])

Percentage of Signatures and Tests completed successfully across one day of multiple sheets

Code:

=Abs([QCflavourChangeSig]+[QCfillerOperatorSig]+[QCblowMouldingSig]+[QCtorqueTestSig]+[QCnetContentsSig]+[QClabellerSig]+[QCpackerSig]+[QCpalletiserSig]+[RMpreformSig]+[RMclosureSig]+[RMlabelSig]+[RMcartonSig]+[QCflavourChange]+[QCfillerOperator]+[QCblowMoulding]+[QCtorqueTest]+[QCnetContents]+[QClabeller]+[QCpacker]+[QCpalletiser]+[RMpreform]+[RMclosure]+[RMlabel]+[RMcarton])/24

Here is the code that is giving me #ERROR messages:

Overall number of Signatures and Tests completed successfully across multiple days of multiple sheets

Code:

=Round(Abs(Avg([QCflavourChangeSig]+[QCfillerOperatorSig]+[QCblowMouldingSig]+[QCtorqueTestSig]+[QCnetContentsSig]+[QClabellerSig]+[QCpackerSig]+[QCpalletiserSig]+[RMpreformSig]+[RMclosureSig]+[RMlabelSig]+[RMcartonSig]+[QCflavourChange]+[QCfillerOperator]+[QCblowMoulding]+[QCtorqueTest]+[QCnetContents]+[QClabeller]+[QCpacker]+[QCpalletiser]+[RMpreform]+[RMclosure]+[RMlabel]+[RMcarton])),2)

Overall percentage of Signatures and Tests completed successfully across multiple days of multiple sheets

Code:

=Abs(Avg([QCflavourChangeSig]+[QCfillerOperatorSig]+[QCblowMouldingSig]+[QCtorqueTestSig]+[QCnetContentsSig]+[QClabellerSig]+[QCpackerSig]+[QCpalletiserSig]+[RMpreformSig]+[RMclosureSig]+[RMlabelSig]+[RMcartonSig]+[QCflavourChange]+[QCfillerOperator]+[QCblowMoulding]+[QCtorqueTest]+[QCnetContents]+[QClabeller]+[QCpacker]+[QCpalletiser]+[RMpreform]+[RMclosure]+[RMlabel]+[RMcarton]))/24

I just can't understand it! The ones that aren't working are simply a combination of the ones that ARE working. I have done some testing and found that the formula fails when I reach the 18th expression. Is there some kind of limit on the number of expressions I can put in a formula? That doesn't seem to add up to me.

Cheers

*very confused*

**ItsMe** · 10-28-2013, 09:36 PM

There is a 255 character limit in some instances. I don't normally use textbox properties for this type of coding. Have you considered using the form's or Report's class module and typing out some VBA. Sorry, but I did not really look at your code. So if I am off base just let me know.

**aaron.irvine** · 10-28-2013, 09:47 PM

Characher limit - that would be it.

When I started writing this report I was very new to Access. I've since written a fair bit of VBA code for some Excel workbooks that I've created. I'd be more than happy to embed the VBA code into the Report's class module... just need to figure out how I would need to format the code

**ItsMe** · 10-28-2013, 10:04 PM

You can try placing these into string variables. You would declare a variable and then have the variable equal your formula. Then you have your control equal the string. you just need to place it in the correct place. Determine where the control is and the name of the control. If the control you are assigning the formula to is in the detail section, you place your VBA in the detail section for format. If it is the page footer, then you place code in the footer format section.

example

Dim strTextBox as string

strTextBox = "Round(Abs(Avg([QCflavourChangeSig]+[QCfillerOperatorSig]+[QCblowMouldingSig]+[QCtorqueTestSig]+[QCnetContentsSig]+[QClabellerSig]+[QCpackerSig"

Me.TextBox.Value = strTextBox

I don't do a lot of large calculations like this. If the string is not large enough then you may need to use a Variant data type.

dim varTextBox as Variant

Anyway, try it out for a few and see if it gets you further in before error

**aaron.irvine** · 10-28-2013, 10:12 PM

Thanks a lot mate! I'll give it a crack

**ItsMe** · 10-28-2013, 10:15 PM

Post if you have any trouble.

**aaron.irvine** · 10-28-2013, 11:26 PM

I feel like such a n00b

I can't figure out where I need to embed this code. I've put it in Private Sub Report_Load() but that's clearly not right.

Code:

Private Sub Report_Load()
Dim strTextBox As String
strTextBox = "=Round(Abs(Avg([QCflavourChangeSig]+[QCfillerOperatorSig]+[QCblowMouldingSig]+[QCtorqueTestSig]+[QCnetContentsSig]+[QClabellerSig]+[QCpackerSig]+[QCpalletiserSig]+[RMpreformSig]+[RMclosureSig]+[RMlabelSig]+[RMcartonSig]+[QCflavourChange]+[QCfillerOperator]+[QCblowMoulding]+[QCtorqueTest]+[QCnetContents]+[QClabeller]+[QCpacker]+[QCpalletiser]+[RMpreform]+[RMclosure]+[RMlabel]+[RMcarton])),2)"
Me.OverallAvgScore.Value = strTextBox

End Sub

I have removed the Control Source from the Text Box - but all I get is xxxxxxxx for an unbound text box.

I'm sure whatever I'm doing is completely laughable right now! I should never try to multi-task....

**ItsMe** · 10-29-2013, 09:51 AM

Try this. I removed the quotes and the extra = sign. I also used a variant data type. You want to place this in the format event for the "Section" your control named "OverallAvgScore" resides.

Maybe it is in a "Footer" section. Maybe it is in a "Header". You need to take a look first. Highlite the section and open a new "Format" event for that section name, maybe "GroupFooter1".

Make sure that your control named "OverallAvgScore" is completely empty. While in design view, it should say "Unbound". If it displays something else, click inside and try to delete any characters, spaces, whatever. Delete it and create a new one if you have to.

Code:

Dim varOverallAvgScore As Variant

varOverallAvgScore = Round(Abs(Avg([QCflavourChangeSig]+[QCfillerOperatorSig]+[QCblowMouldingSig]+[QCtorqueTestSig]+[QCnetContentsSig]+[QClabellerSig]+[QCpackerSig]+[QCpalletiserSig]+[RMpreformSig]+[RMclosureSig]+[RMlabelSig]+[RMcartonSig]+[QCflavourChange]+[QCfillerOperator]+[QCblowMoulding]+[QCtorqueTest]+[QCnetContents]+[QClabeller]+[QCpacker]+[QCpalletiser]+[RMpreform]+[RMclosure]+[RMlabel]+[RMcarton])),2)

Me.OverallAvgScore.Value = varOverallAvgScore

**aaron.irvine** · 10-29-2013, 05:43 PM

Hey ItsMe,

I'm feeling exceptionally dumb now!

The Text Box is in the Report Footer as can be seen in the attachment.

I've created the event in the code as such:

Code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim varOverallAvgScore As Variant
varOverallAvgScore = Round(Abs(Avg([QCflavourChangeSig] + [QCfillerOperatorSig] + [QCblowMouldingSig] + [QCtorqueTestSig] + [QCnetContentsSig] + [QClabellerSig] + [QCpackerSig] + [QCpalletiserSig] + [RMpreformSig] + [RMclosureSig] + [RMlabelSig] + [RMcartonSig] + [QCflavourChange] + [QCfillerOperator] + [QCblowMoulding] + [QCtorqueTest] + [QCnetContents] + [QClabeller] + [QCpacker] + [QCpalletiser] + [RMpreform] + [RMclosure] + [RMlabel] + [RMcarton])), 2)
Me.OverallAvgScore.Value = varOverallAvgScore
End Sub

I'm getting a blank output from the control though. At least it isn't saying #ERROR anymore

Click image for larger version.

Name: OverallAvgScore.png
Views: 9
Size: 22.9 KB
ID: 14224

**ItsMe** · 10-29-2013, 05:48 PM

Don't worry about it. I am the one that left the quotes in, etcetera. Let me take a look and see what might be going on.

**June7** · 10-29-2013, 05:53 PM

If you want the average of the sum of all those fields over all records, that can be done. However, if any field is Null the result will be Null. Arithmetic calcs with Null return Null. Handle possible Null with Nz().

(Nz([QCflavourChangeSig],0) + Nz(...

Could it be possible that all the expression fields of a single record could be Null? Should that record be excluded from the overall average?

**ItsMe** · 10-29-2013, 05:58 PM

Thanks june, I was just about to start testing that.

Let me see if I can get a UDF together for the OP

**June7** · 10-29-2013, 06:07 PM

Here is one that will sum an array of values and return Null if all inputs are Null:

Code:

Function RSum(ParamArray FieldValues()) As Variant
'--------------------------------------------------
' Function RSum() adds all the arguments passed to it.
' If all arguments do not contain any data, RSum returns null.
'--------------------------------------------------
Dim dblTotal As Double, blnValid As Boolean
Dim varArg As Variant
For Each varArg In FieldValues
   If IsNumeric(varArg) Then
      blnValid = True
      dblTotal = dblTotal + varArg
   End If
Next
If blnValid Then ' One of the arguments was a number.
   RSum = dblTotal
Else  ' No valid points to add.
   RSum = Null
End If
End Function

Call it like:

Round(Abs(Avg(RSum([QCflavourChangeSig], [QCfillerOperatorSig], ...

However, If OP doesn't care to exclude the 'all Nulls' record, then just use the Nz.

**aaron.irvine** · 10-29-2013, 06:14 PM

The thing is guys, referring to the attachment that I posted, both the QC ONLY and the SIG ONLY fields for both totals and percentage work fine. The OVERALL where I simply combine the calculations together doesn't work, primarily because of the 255 char limit.

The easy solution would be to simply average the two totals from the respective sub-totals QC ONLY and SIG ONLY, but I can't even get that to work!

**ItsMe** · 10-29-2013, 06:39 PM

Well, right now I am having a hard time getting Avg to work outside of a query. Right now, the only option I am seeing is to build this out in a querry somehow. There is a built in function Davg. But it is a domain function and sounds expensive. Plus, without your values in a table or querry domain, Davg won't work.

Is your DB something that you can eleminate private data and upload?

Code Error - Can you have too many expressions?

Thread Tools

Code Error - Can you have too many expressions?

Similar Threads

Error message code

Simplifying expressions by assigning parts to variables error

code logic error

Error in Code

(simple) Expressions give error message

Posting Permissions