Expression too complex error

[Wongy]

I have read as much as I can on this in the threads but I'm still not sure how to do this. My apologies if my predecessor pinched the code for function

I have inherited a database which has a form where questions are answered via a series of combo boxes with the possible answers being yes/no/NA. A text box at the top of the form calculates the average score via a function as each answer is given. This works when 28 variables were passed to the function.

The code in the text box's control source is simply = Work([Q1],[Q2],[Q3],[Q4],[Q5],[Q6],[Q7]....[Q28])

but now my boss wants to increase the number of variables to 41. I have read that there is a max of 29? variables and I have also read that you can get around this by docmd.runsql but I'm not sure how to do this as I hardly ever use Access/VBA

Using what little knowledge I have I have written

Dim sql As String
sql = "select Work([Q1],[Q2],[Q3],[Q4],[Q5],[Q6],[Q7]..[Q41])from data"
DoCmd.RunSQL sql
End Sub

but I'm not sure how to get this to display in the form. Can anyone help me
thanks

 

[ProgramError]

Are q1 q2 q3 ect part of an array or are they individual variables?



Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[Wongy]

Thanks

they are individual variables
i.e. question 1, question 2

 

[TheAceMan1]

[blue] . . . they are individual variables
i.e. question 1, question 2 . . .[/blue][/code]
Acutal [blue]variables in VBA[/blue] or [blue]controls on the form[/blue]?

What is the function doing with the variables?

I'm thinking a [blue]looping structure[/blue] that increments the numeric portion of the question names during processing. This way you could have as many questions as you like . . .

See Ya! . . . . . .

 

[Wongy]

Hi they are controls on the form. The database is used to quality assure our processes. The form asks questions to do with judgement, decision making, customer service and performance.

Each question can return either 1 for yes , 0 for no, and 9 for not applicable

The function takes these scores and calculates an average score for all questions and an average for subsets e.g.,
Overall 70%
Performance 20%
Judgement 80%
Decision Making 100%
Customer Service 90%

The function is:
Function Work(A, Optional B, Optional c, Optional D, Optional E, Optional F, .......Optional z16) As Variant

Dim CT As Integer
Dim SM As Integer
Dim Nine As Integer

If A = 1 Then SM = SM + 1
If A = 1 Then CT = CT + 1
If A = 0 Then CT = CT + 1
If A = 9 Then Nine = 9

If IsMissing(B) Then GoTo Sumit
If B = 1 Then SM = SM + 1
If B = 1 Then CT = CT + 1
If B = 0 Then CT = CT + 1
If B = 9 Then Nine = 9

If IsMissing(c) Then GoTo Sumit
If c = 1 Then SM = SM + 1
If c = 1 Then CT = CT + 1
If c = 0 Then CT = CT + 1
If c = 9 Then Nine = 9
........

If IsMissing(Z16) Then GoTo Sumit
If Z12 = 1 Then SM = SM + 1
If Z12 = 1 Then CT = CT + 1
If Z12 = 0 Then CT = CT + 1
If Z12 = 9 Then Nine = 9

If CT = 1 And SM = 0 And Nine = 0 Then SM = 1
If CT = 0 And SM = 0 And Nine = 9 Then GoTo Nuller
If CT = 0 Then CT = 1

Work = SM / CT

Exit Function

Sumit:

If CT = 1 And SM = 0 And Nine = 0 Then SM = 1
If CT = 0 And SM = 0 And Nine = 9 Then GoTo Nuller
If CT = 0 Then CT = 1

Work = SM / CT

Exit Function

Nuller:

Work = Null

Exit Function

End Function

 

[TheAceMan1]

Roger That Wongy . . .

Before I get to code . . . am I to understand [blue]the first empty control in the sequence[/blue] causes summations to end and the answer returned only involves those answers up to that point?

Also are you saying you have a seperate textbox for each group calculation that only passes the controls for that group?

See Ya! . . . . . .

 

[Wongy]

Thanks for you time so far

I haven't considered that before but reading the code properly yes you are correct. The default answer is N/A so it shouldn't be a problem.

 

[TheAceMan1]

. . . and the second question?

See Ya! . . . . . .

 

[Wongy]

Sorry Yes

the group ones are OK as they don't exceed the max number of variables