weird module

[hermie]

I created a module and now it is working not good and not know what causes
the problem

the module =
Public Function caltarea(Pertar As String) As String

Select Case Pertar
Case 90# To 100#
caltarea = "A"
Case 80# To 89#
caltarea = "B"
Case 70# To 79#
caltarea = "C"
Case 60# To 69#
caltarea = "D"
Case 0# To 59#
caltarea = "F"

End Select
End Function

The result in the query is
valor puntos Percent Nota
24 24 100.00 F
22 24 91.67
26 29 89.66
32 36 88.89 B
19 24 79.17
17 24 70.83 C
25 36 69.44
20 29 68.97 D
21 36 58.33 F
7 24 29.17 F
2 24 8.33
1 36 2.78 F
1 36 2.78 F
0 36 0.00 F
#Error


Why have percent 100.00 a F it should be an A
and 8.33 is blank should be an F and for 69.44 shoul be a D

Any help is welcome

 

[Douglas J. Steele]

Not 100% sure about why you're getting F rather than A for 100, but you're
not getting D for 69.44 because "60# to 69#" means between 60 and 69
inclusive, and 69.44 isn't in that range (it's greater than 69). Also, your
function is looking for a string as input, yet you're comparing it to
doubles.

Try changing your function to:

Public Function caltarea(Pertar As String) As String

Select Case CDbl(Pertar)
Case Is >= 90#
caltarea = "A"
Case Is >= 80# And Pertar < 90#
caltarea = "B"
Case Is >= 70# And Pertar < 80#
caltarea = "C"
Case Is >= 60# And Pertar < 70#
caltarea = "D"
Case Is < 60#
caltarea = "F"
Case Else
caltarea = "?"
End Select
End Function

 

[Tim Ferguson]

Public Function caltarea(Pertar As String) As String

The parameter Pertar is defined as a string, and you seem to be handling
it as a number -- not sure how this affects your problem but the first
step would be to get all your data typing right.

You could either coerce the string into a number before passing it:

debug.print caltarea(CDbl(Percent))

or after it

Public Function caltarea(Pertar as string) _
as string

dim dbl As Double

dbl = CDbl(Pertar)
Select Case...

There is a problem with values of, for example, 89.5 which would not get
filtered at all. With such a simple selection, I would be tempted to use
a ElseIf structure anyway:

If dbl < 60 Then
caltarea = "F"
elseif dbl < 70 Then
caltarea = "D"
etc

or use some simple maths

caltarea = Mid$("FDCBA", _
iif(dbl<50, 1, _
iif(dbl=100, 5, _
int(0.1 * (dbl-40)))), 1)




Hope that helps


Tim F

 

[hermie]

Thanks Douglas it works great now

Not 100% sure about why you're getting F rather than A for 100, but you're
not getting D for 69.44 because "60# to 69#" means between 60 and 69
inclusive, and 69.44 isn't in that range (it's greater than 69). Also, your
function is looking for a string as input, yet you're comparing it to
doubles.

Try changing your function to:

Public Function caltarea(Pertar As String) As String

Select Case CDbl(Pertar)
Case Is >= 90#
caltarea = "A"
Case Is >= 80# And Pertar < 90#
caltarea = "B"
Case Is >= 70# And Pertar < 80#
caltarea = "C"
Case Is >= 60# And Pertar < 70#
caltarea = "D"
Case Is < 60#
caltarea = "F"
Case Else
caltarea = "?"
End Select
End Function