Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Textboxes

Status
Not open for further replies.

generaluser

Technical User
May 24, 2002
79
US
I created functions in a modules and had their answers show in textboxes in a report. I now want to do averages. However when I create a new textbox and make the control source =Avg([text139]) and go to design view,it asks me for a parameter. How do I fix it?

Thanks
 
Hi!

Copy the control source for text139 into the () of the average function. Access will not average a calulated control.

hth
Jeff Bridgham
bridgham@purdue.edu
 
The control source is =qa_score().

Should I put it =Avg([qa_score()]) or =Avg(qa_score())
 
Hi!

If either will work it will be =Avg(qa_score()). I have not tried putting a public function as a argument for an intrisnic function. It may be that you will need to write an new function to figure the average.

hth
Jeff Bridgham
bridgham@purdue.edu
 
I tried writing a new function to distribute the average but it doesn't work. Can you show me what you would do? Thanks
 
Hi!

If you could post the code for qa_score() that might help me along. It would be ideal if you could email me a zipped copy of your db.

Jeff Bridgham
bridgham@purdue.edu
 
qa_score() code

Function qa_score()
Select Case Reports!rpt_scorecard!level
Case 1 To 2
Select Case Reports!rpt_scorecard![tbl_productivity.qa]
Case 0 To 0.79
qa_score = 1
Case Is >= 0.9
qa_score = "4"
Case 0.85 To 0.89
qa_score = "3"
Case 0.8 To 0.84
qa_score = "2"
Case Else
qa_score = " "
End Select
Case 3
Select Case Reports!rpt_scorecard![tbl_productivity.qa]
Case 0 To 0.84
qa_score = 1
Case Is >= 0.95
qa_score = 4
Case 0.9 To 0.94
qa_score = 3
Case 0.85 To 0.89
qa_score = 2
Case Else
qa_score = " "
End Select
Case 4
Select Case Reports!rpt_scorecard![tbl_productivity.qa]
Case 0 To 0.84
qa_score = 1
Case 0.99 To 1
qa_score = "4"
Case 0.95 To 0.98
qa_score = "3"
Case 0.85 To 0.94
qa_score = 2
Case Else
qa_score = " "
End Select
End Select

Would it have been better to create a field in the query and do this there and then do the average in the report?
If so, how do you change "Reports!rpt_scorecard!level" to make it work.
 
Hi!

If level and qa are fields in a table(s) then you can do it in query:

Function qa_score(qalevel as Long, qaprod As Double) As String
Select Case qalevel
Case 1 To 2
Select Case qaprod
Case 0 To 0.79
qa_score = 1
Case Is >= 0.9
qa_score = "4"
Case 0.85 To 0.89
qa_score = "3"
Case 0.8 To 0.84
qa_score = "2"
Case Else
qa_score = " "
End Select
Case 3
Select Case qaprod
Case 0 To 0.84
qa_score = 1
Case Is >= 0.95
qa_score = 4
Case 0.9 To 0.94
qa_score = 3
Case 0.85 To 0.89
qa_score = 2
Case Else
qa_score = " "
End Select
Case 4
Select Case qaprod
Case 0 To 0.84
qa_score = 1
Case 0.99 To 1
qa_score = "4"
Case 0.95 To 0.98
qa_score = "3"
Case 0.85 To 0.94
qa_score = 2
Case Else
qa_score = " "
End Select
End Select

Then in the query design view create the following field:

qascore: qa_score([YourTable].[level], [YourTable].[qa])

hth
Jeff Bridgham
bridgham@purdue.edu
 
Thanks for the suggestion.

However,

Now where it says Case Else
qa_score = " "

It gives me a #Error in the query. It never did that in the
report though. Any thoughts on why. Please let me know.
 
Hi!

Glad you got it going! How did you fix it? I had some ideas, but I am willing to hear what actually worked!

Jeff Bridgham
bridgham@purdue.edu
 
Well, I had to take out As Long and As Double in the parameter. But, now when I drag the fields from the query into the report it says I have mismatched data types in the criteria expression. It won't open at all even though it works in the query. Any suggestions?
 
Hi!

In your function declaration change the As String to As Integer. And all of the qa_score="4" etc should be changed to qa_score=4 etc.

hth
Jeff Bridgham
bridgham@purdue.edu
 
I can't Do that either because my Case Else is
" ". It still says type Mismatch
 
Hi!

Why not just change it to qa_score=0. This will allow that record to be counted without adding anything else, just like using "" as a string. If you want to ingor these values for averaging then create your own average:

=Sum(qascore)/Sum(IIf(qascore=0),0,1))

hth
Jeff Bridgham
bridgham@purdue.edu
 
I can't use score. Inside the report, I want to take the
average of qa_score. We don't want the employees to be
held accountable if there is no score for the month.
What if I don't include the Select Else? Will it just
not put a value for qa_score?
 
I think I got it. I took out all the Case Else's.
It will still return a null value when none of the
criteria is met. I can now open my report. Yea!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top