Type Mismatch Error at runtime but not during debugging 1

[MarineMike1985]

Greeting all,

Thank you all very much for taking the time to look at this question.

BACKGROUND:
I am new to VBA but, not to programming. I am working on a way of tracking and evaluating fitness reports. The fitness reports have 14 different points of evaluations. Each point is given a score from A to H, with A being the worst, G the best, and H being a "Not observed or leave out.

ISSUE:
My issue with with a function I wrote which takes an array of the scores and finds the numerical average for the report (SEE CODE). A=1, B=2, etc. When this function is run at runtime and I try to return a value to a variable within another funtion I get a Type Mismatch error. But, when I wrote a "test" function to try an debug my error it works fine.

QUESTION:
Can anyone point out to me where I have screwed this up? Why does this work when I try and debug it but not at runtime?

Thank you very much for your time!

CODE:
Function ReportAverage(ByRef scores() As String)
Dim scr As Variant
'for Debugging print all of the scores given in the parameter
For Each scr In scores
Debug.Print scr
Next

Dim total As Double
Dim divider As Double
Dim score As Variant
Dim i As Integer

divider = UBound(scores)

For i = 1 To UBound(scores)
'If the score is "H" then that catagory is not counted in the average for this report
'Therefore the divider is reduced by 1
If (scores(i) = "H") Then
divider = divider - 1
Debug.Print "Skipped scores(" & i & ")"
GoTo Continue1
End If

Debug.Print "Scores(" & i & ") = " & scores(i)
'add the score to the total score
'LetterToScore is an additional function which converts the letter of a score into an integer
'which is then used to compute the average
total = total + LetterToScore(scores(i))
Continue1:
Next i
'The average of the report is simply the total score over the number of scores recorded
ReportAverage = total / divider
Debug.Print total / divider


End Function
Function testing()
Dim scores(1 To 14) As String

scores(1) = "D"
scores(2) = "D"
scores(3) = "D"
scores(4) = "D"
scores(5) = "D"
scores(6) = "D"
scores(7) = "D"
scores(8) = "D"
scores(9) = "D"
scores(10) = "D"
scores(11) = "D"
scores(12) = "D"
scores(13) = "D"
scores(14) = "D"

Debug.Print ReportAverage(scores)

End Function

Function SubmitBtn()
...
Dim scores(1 to 14) as String
Dim RptAvg as Double
...
'get the scores from the ReportForm
...
RptAvg = ReportAverage(scores) <- This is where I get the error
...
End Function

 

[SkipVought]

hi,

Function SubmitBtn()
    Dim scores(1 To 14) As String
    Dim RptAvg As Double
    
    'get the scores from the ReportForm

'[b][highlight]You never load the scores array!!![/highlight][/b]
    
    RptAvg = ReportAverage(scores) '<- This is where I get the error

End Function

What application?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, both Total and Divider are Integer in actuality.

Also the numeric equivalent, "A=1, B=2, etc." is simply...

total = total + Asc(scores(i)) - 64

Both the test code & button runs without error, although the latter has no data in the array.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MarineMike1985]

First, Thank you very much for responding so quickly!

Next, I apologize I should have explained in better detail.
1. I am programming for MS Access 2010.

2. For the reports I am averaging there are a predetermined value for each of the letters. (I did not define the values, they were defined for me by the document which governs these reports.) This is why I wrote "LetterToScore(letter As String)" It takes the letter and returns the predetermined int value for that letter using a Select Case statement.

3. I understand that the total and divider are both integers, but I defined them as doubles because the average is going to be a double and it should return a double. I defined them as doubles because I didn't want to do integer division.

Does VBA treat these as Integer anyway? Or does VBA redefine those variables when I put an int into it?

4. I also apologize for the confusion on the SubmitBtn(). That function is about 350 lines long and includes all of the form validation code. The scores() array is filled during the validation. Here is the rest of the form's code minus a lot of the debug code.

Thank you again for your help!

Private Sub Submit_Click()

Dim error As String

Dim Process As String

Dim ReportID As Integer
Dim MROID As Integer
Dim Last4 As String
Dim Grade As String
Dim lName As String
Dim BMOS As String
Dim Command As String
Dim billet As String

Dim FROMDate As Date
Dim TODate As Date

Dim OCC As String
Dim TypeReport As String

Dim comm As Boolean
Dim adverse As Boolean
Dim Promote As Boolean


Dim scores(1 To 14) As String
Dim scorePerformance As String
Dim scoreProficiency As String
Dim scoreCourage As String
Dim scoreEff As String
Dim scoreInitative As String
Dim scoreLeading As String
Dim scoreDeveloping As String
Dim scoreSetting As String
Dim scoreEnsuring As String
Dim scoreCommunication As String
Dim scoreProfessional As String
Dim scoreDecision As String
Dim scoreJudgment As String
Dim scoreEvaluations As String

Dim RptAvg As Double
Dim GradeAverage As Double
Dim ReportAtHigh As Integer
Dim ReportDate As Date
Dim RN As Integer
Dim RVatProc As Double

'Get the heading data
Process = Me.Process.Value
If (Process = "UPDATE") Then
ReportID = Me.ReportID.Value
End If
MROID = Me.MROID.Value
Grade = Me.Grade.Value
lName = Me.lName.Value
Last4 = Me.Last4.Value
BMOS = Me.BMOS.Value
Command = Me.Command.Value
billet = Me.BilletDescription.Value

'''''''''''''''''''''
'Make sure that all
'of the fields are
'filled.
'''''''''''''''''''''
'Dates
If (Me.FROMDate.Value <> "") Then
FROMDate = Me.FROMDate.Value
Else
error = "FROM DATE"
GoTo Err_noData
End If

If (Me.TODate.Value <> "") Then
TODate = Me.TODate.Value
Else
error = "TO DATE"
GoTo Err_noData
End If
If (Me.OCC.Value <> "") Then
OCC = Me.OCC.Value
Else
error = "OCC"
GoTo Err_noData
End If
If (Me.Type.Value <> "") Then
TypeReport = Me.Type.Value
Else
error = "Type"
GoTo Err_noData
End If

'commendatory, adverse, and promote
If (Me.CommendCheck And Me.AdverseCheck) Then
error = "This report cannot be both Commendatory and Adverse"
GoTo Err_noData
End If
If (Me.CommendCheck.Value = -1) Then
comm = Me.CommendCheck.Value
ElseIf (Me.AdverseCheck.Value = -1) Then
adverse = Me.AdverseCheck.Value
End If

prmote = Me.PromoteCheck.Value

'get the scores
If (Me.scorePerformance.Value <> "") Then
scorePerformance = Me.scorePerformance.Value
scores(1) = Me.scorePerformance.Value
Else
error = "Performance"
GoTo Err_noData
End If

If (Me.scoreProficiency.Value <> "") Then
scoreProficiency = Me.scoreProficiency.Value
scores(2) = Me.scoreProficiency.Value
Else
error = "Prociciency"
GoTo Err_noData
End If

If (Me.scoreCourage.Value <> "") Then
scoreCourage = Me.scoreCourage.Value
scores(3) = Me.scoreCourage.Value
Else
error = "Courage"
GoTo Err_noData
End If

If (Me.scoreEff.Value <> "") Then
scoreEff = Me.scoreEff.Value
scores(4) = Me.scoreEff.Value
Else
error = "Effectiveness under stress"
GoTo Err_noData
End If

If (Me.scoreInitative.Value <> "") Then
scoreInitative = Me.scoreInitative.Value
scores(5) = Me.scoreInitative.Value
Else
error = "Initative"
GoTo Err_noData
End If

If (Me.scoreLeading.Value <> "") Then
scoreLeading = Me.scoreLeading.Value
scores(6) = Me.scoreLeading.Value
Else
error = "Leading Subordinates"
GoTo Err_noData
End If

If (Me.scoreDeveloping.Value <> "") Then
scoreDeveloping = Me.scoreDeveloping.Value
scores(7) = Me.scoreDeveloping.Value
Else
error = "Developing Subordinates"
GoTo Err_noData
End If

If (Me.scoreSetting.Value <> "") Then
scoreSetting = Me.scoreSetting.Value
scores(8) = Me.scoreSetting.Value
Else
error = "Setting the example"
GoTo Err_noData
End If

If (Me.scoreEnsuring.Value <> "") Then
scoreEnsuring = Me.scoreEnsuring.Value
scores(9) = Me.scoreEnsuring.Value
Else
error = "Ensuring Well-being of subordinates"
GoTo Err_noData
End If

If (Me.scoreCommunication.Value <> "") Then
scoreCommunication = Me.scoreCommunication.Value
scores(10) = Me.scoreCommunication.Value
Else
error = "Communication Skills"
GoTo Err_noData
End If

If (Me.scoreProfessional.Value <> "") Then
scoreProfessional = Me.scoreProfessional.Value
scores(11) = Me.scoreProfessional.Value
Else
error = "Professional Military Education"
GoTo Err_noData
End If

If (Me.scoreDecision.Value <> "") Then
scoreDecision = Me.scoreDecision.Value
scores(12) = Me.scoreDecision.Value
Else
error = "Decision making ability"
GoTo Err_noData
End If

If (Me.scoreJudgment.Value <> "") Then
scoreJudgment = Me.scoreJudgment.Value
scores(13) = Me.scoreJudgment.Value
Else
error = "Judgment"
GoTo Err_noData
End If

If (Me.scoreEvaluations.Value <> "") Then
scoreEvaluations = Me.scoreEvaluations.Value
scores(14) = Me.scoreEvaluations.Value
Else
error = "Evaluations"
GoTo Err_noData
End If

RptAvg = ReportAverage(scores)
GradeAverage = RSAvg(Grade)
ReportAtHigh = ReportsAtHigh(Grade)
ReportDate = Date
'RN = ReportNumber(Grade)
If (Process = "NEW") Then
RN = RN + 1
End If
RVatProc = CumRV(Grade, RptAvg)

''''''''''''''''''''''''''''''
'If a New Fitness Report
'then use an INSERT query
'
'INSERT INTO "table_name" ("column1", "column2", ...)
'VALUES ("value1", "value2", ...)
'
'If an Update Fitness Report
'then use an UPDATE query
'
'UPDATE table_name
'SET column1=value, column2=value2,...
'WHERE some_column = some_value
''''''''''''''''''''''''''''''

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim INSERT_SQL As String
Dim UPDATE_SQL As String

INSERT_SQL = "INSERT INTO Reports (MROID,Last4,lName,Grade,BMOS,OCC,Type,FROMDate,TODate,Com,Adv,BilletDescription,Command,Promote,Performance,Proficiency,Courage,Stress,Initiative,Leading,Developing,Example,WellBeing,Communicate,PME,Decisions,Judgment,Evaluations,ReportAverage,GradeAverage,ReportAtHigh,ReportDate,ReportNumber,RVatProc) " & _
"VALUES (" & MROID & ",'" & Last4 & "','" & lName & "','" & Grade & "','" & BMOS & "','" & OCC & "','" & TypeReport & "','" & FROMDate & "','" & TODate & "','" & comm & "','" & adverse & "','" & billet & "','" & Command & "','" & Promote & "','" & scorePerformance & "','" & scoreProficiency & "','" & scoreCourage & "','" & scoreEff & "','" & scoreInitative & "','" & scoreLeading & "','" & scoreDeveloping & "','" & scoreSetting & "','" & scoreEnsuring & "','" & scoreCommunication & "','" & scoreProfessional & "','" & scoreDecision & "','" & scoreJudgment & "','" & scoreEvaluations & "','" & RptAvg & "','" & GradeAverage & "','" & ReportAtHigh & "','" & ReportDate & "','" & RN & "','" & RVatProc & "');"

UPDATE_SQL = "UPDATE Reports SET MROID = " & MROID & ", lName = '" & lName & "', Grade = '" & Grade & "', BMOS = '" & BMOS & "', OCC = '" & OCC & "', Type = '" & TypeReport & "', FROMDate = '" & FROMDate & "', TODate = '" & TODate & "', Com = '" & comm & "', Adv = '" & adverse & "', BilletDescription = '" & billet & "', Command = '" & Command & "', Promote = '" & Promote & "', Performance = '" & scorePerformance & "', Proficiency = '" & scoreProficiency & "', Courage = '" & scoreCourage & "', Stress = '" & scoreEff & "', Initiative = '" & scoreInitative & "', Leading = '" & scoreLeading & "', Developing = '" & scoreDeveloping & "', Example = '" & scoreSetting & "', WellBeing = '" & scoreEnsuring & "', Communicate = '" & scoreCommunication & "', PME = '" & scoreProfessional & "', Decisions = '" & scoreDecision & "', Judgment = '" & scoreJudgment & "', Evaluations = '" & scoreEvaluations & "', ReportAverage = '" & RptAvg & "', GradeAverage = '" & GradeAverage & "', ReportAtHigh = '" & ReportAtHigh & _
"', ReportDate = '" & ReportDate & "', ReportNumber = '" & RN & "', RVatProc = '" & RVatProc & "' WHERE ID = " & ReportID & ";"

Set dbs = CurrentDb()

If (Process = "NEW") Then
dbs.Execute INSERT_SQL
Debug.Print "INSERTING NEW FitRep"
Debug.Print INSERT_SQL
ElseIf (Process = "UPDATE") Then
dbs.Execute UPDATE_SQL
Debug.Print "UPDATING FitRep"
Debug.Print UPDATE_SQL
End If

DoCmd.Close

Dim FormName As String

FormName = "FormReviewMROFitReps"
Call UpdateRSAvg(Grade)
Call OpenReviewMROFitReps(MROID)

Exit_Submit_Click:
Exit Sub

Err_noData:
MsgBox error & " ERROR"
GoTo Exit_Submit_Click

End Sub

 

[SkipVought]

    RptAvg = ReportAverage(scores) '<- This is where I get the error

The only thing that I see is that your function returns a Variant and your receiving variable is a Double.

Try making it a Variant.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

What error do you have?
Try to set "break on all errors" or "break in class modules" general option in the vbe. Next execute the code line by line (add a breakpoint and run the form).

combo

 

[MarineMike1985]

combo, Great minds thing alike! I've been stepping through my code using break points. I love them its the fastest way I've found to debug my code.

skip, I tried defining the "RptAvg" to a varient, then I tried making both the RptAvg and ReportAverage Double. Neither worked.

What I did was retype exactly what ReportAverage says into a new function. THIS WORKED!

WHAT THE HECK?!?!

I really hate unprintable characters! At least that is the only thing I can think of as to why the first function didn't work and my new one did.

Skip, can you tell me what I've done differently? I find this baffling.

Let's play "What's the difference?!" except the comments and debugging code.

Function ReportAverage(scores() As String)
Dim scr As Variant
'for Debugging print all of the scores given in the parameter
For Each scr In scores
Debug.Print scr
Next

Dim total As Double
Dim divider As Double
Dim score As Variant
Dim i As Integer

divider = UBound(scores)

For i = 1 To UBound(scores)
'If the score is "H" then that catagory is not counted in the average for this report
'Therefore the divider is reduced by 1
If (scores(i) = "H") Then
divider = divider - 1
Debug.Print "Skipped scores(" & i & ")"
GoTo Continue1
End If

Debug.Print "Scores(" & i & ") = " & scores(i)
'add the score to the total score
'LetterToScore is an additional function which converts the letter of a score into an integer
'which is then used to compute the average
total = total + LetterToScore(scores(i))
Continue1:
Next i
'The average of the report is simply the total score over the number of scores recorded
ReportAverage = total / divider
Debug.Print total / divider


End Function

Function RepAvg(scores As Variant)
Dim scr As Variant
For Each scr In scores
Debug.Print scr
Next

Dim total As Double
Dim divider As Double

Dim i As Integer

divider = UBound(scores)

For i = 1 To UBound(scores)
If (scores(i) = "H") Then
divider = divider - 1
Debug.Print "Skipped scores(" & i & ")"
GoTo Continue1
End If

Debug.Print "Scores(" & i & ") = " & scores(i)
total = total + LetterToScore(scores(i))
Continue1:
Next i

Debug.Print total / divider
RepAvg = total / divider

End Function

 

[MarineMike1985]

Skip and combo,
Thank you very much for your time. I really appreciate your help. I look forward to learning more and helping others.

 

[SkipVought]

Thanks! That's what Tek-Tips is all about!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]