Help with Function Code

[Ken Little]

I am trying to write some code for a Function called AlScore. However, I
get the error message:
"Compile Error: Argument Not Optional"
and the last IIF is highlighted in Blue.

Public Function AlScore(PctScore)
AlScore = IIf(PctScore < 0.5, 11, IIf(PctScore >= 0.5 _
And PctScore <= 0.54999, 10, IIf(PctScore >= 0.55 _
And PctScore <= 0.59999, 9, IIf(PctScore >= 0.6 _
And PctScore <= 0.64999, 8, IIf(PctScore >= 0.65 _
And PctScore <= 0.69999, 7, IIf(PctScore >= 0.7 _
And PctScore <= 0.74999, 6, IIf(PctScore >= 0.75 _
And PctScore <= 0.79999, 5, IIf(PctScore >= 0.8 _
And PctScore <= 0.84999, 4, IIf(PctScore >= 0.85 _
And PctScore <= 0.89999, 3, IIf(PctScore >= 0.9 _
And PctScore <= 0.94999, 2, IIf(PctScore >= 0.95 _
And PctScore <= 1, 1)))))))))))
End Function

Any ideas? - Ken

 

[Ken Little]

I see that I didn't have a false argument in the last IIF statement. I have
also been informed that it is better to use IF...THEN...ELSE statements
instead of IIF statements. Could someone show me the syntax?

Also, using the And operator between the operations, I was trying to say
"Between "x" and "y" inclusively. WOuld this be the correct way. In MS
Access queries and in SQL I just use "BETWEEN...AND" , but this doesn't seem
to work in VBA. Thanks in advance.

Ken

 

[Douglas J. Steele]

Actually, I'd use a SELECT CASE statement, rather than IF...THEN...ELSE

Public Function AlScore(PctScore As Single) As Integer
Select Case PctScore
Case Is < 0.5
AIScore = 11
Case Is >= 0.5 And PctScore < 0.55
AIScore = 10
Case Is >= 0.55 And PctScore < 0.6
AIScore = 9
Case Is >= 0.6 And PctScore < 0.65
AIScore = 8
Case Is >= 0.65 And PctScore < 0.7
AIScore = 7
Case Is >= 0.7 And PctScore < 0.75
AIScore = 6
Case Is >= 0.75 And PctScore < 0.8
AIScore = 5
Case Is >= 0.8 And PctScore < 0.85
AIScore = 4
Case Is >= 0.85 And PctScore < 0.9
AIScore = 3
Case Is >= 0.8 And PctScore < 0.95
AIScore = 2
Case Is >= 0.95 And PctScore <= 1
AIScore = 1
Case Else
AIScore = 0
End Select
End Function

It's should be possible to express this much simpler by creating a
mathematical function that returns the integer you want. I'm afraid I'm just
too lazy to try and figure it out right now!

Note that checking PctScore >= 0.5 And PctScore <= 0.54999 then PctScore >=
0.55 And PctScore <= 0.59999 theoretically misses values between .54999 and
..55. That's why I've changed the upper bounds to < the same number as the >=
lower bound in the next statement. Given that there are errors introduced
using floating point numbers, this isn't as far fetched as it may sound.

 

[Ken Little]

Doug,

Thanks for the rewrite. I am just learning code and I am not familiar with
the "Select Case" code. I will cut and paste and try it out.

As for your suggestions on changing the upper bound, you are on the money
here as well. I'll give it a shot! Thanks!

Ken

 

[Ken Little]

Doug,

One last thing. I cut and pasted the code into a query and when PctScore =
..803 the code returned a value of "10." The code in the query was: AlScore:
Alscore([PctScore]) . When I run the query, the PctScore calculated value
is .803. Theoretically, I would have thought the AIScore would have been a
"4," but it was "10." Any ideas?

Ken

 

[Douglas J. Steele]

That should have worked! In looking at it again, though, I realized you
don't need the >= part of the expression. That's part of the beauty of using
Case statements: it goes through them in sequence until it finds one that it
matches.

Public Function AIScore(PctScore As Single) As Integer
Select Case PctScore
Case Is < 0.5
AIScore = 11
Case Is < 0.55
AIScore = 10
Case Is < 0.6
AIScore = 9
Case Is < 0.65
AIScore = 8
Case Is < 0.7
AIScore = 7
Case Is < 0.75
AIScore = 6
Case Is < 0.8
AIScore = 5
Case Is < 0.85
AIScore = 4
Case Is < 0.9
AIScore = 3
Case Is < 0.95
AIScore = 2
Case Is <= 1
AIScore = 1
Case Else
AIScore = 0
End Select

End Function


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Doug,

One last thing. I cut and pasted the code into a query and when PctScore =
.803 the code returned a value of "10." The code in the query was: AlScore:
Alscore([PctScore]) . When I run the query, the PctScore calculated value
is .803. Theoretically, I would have thought the AIScore would have been a
"4," but it was "10." Any ideas?

Ken

Actually, I'd use a SELECT CASE statement, rather than IF...THEN...ELSE

Public Function AlScore(PctScore As Single) As Integer
Select Case PctScore
Case Is < 0.5
AIScore = 11
Case Is >= 0.5 And PctScore < 0.55
AIScore = 10
Case Is >= 0.55 And PctScore < 0.6
AIScore = 9
Case Is >= 0.6 And PctScore < 0.65
AIScore = 8
Case Is >= 0.65 And PctScore < 0.7
AIScore = 7
Case Is >= 0.7 And PctScore < 0.75
AIScore = 6
Case Is >= 0.75 And PctScore < 0.8
AIScore = 5
Case Is >= 0.8 And PctScore < 0.85
AIScore = 4
Case Is >= 0.85 And PctScore < 0.9
AIScore = 3
Case Is >= 0.8 And PctScore < 0.95
AIScore = 2
Case Is >= 0.95 And PctScore <= 1
AIScore = 1
Case Else
AIScore = 0
End Select
End Function

It's should be possible to express this much simpler by creating a
mathematical function that returns the integer you want. I'm afraid I'm just
too lazy to try and figure it out right now!

Note that checking PctScore >= 0.5 And PctScore <= 0.54999 then PctScore
=
0.55 And PctScore <= 0.59999 theoretically misses values between .54999 and
.55. That's why I've changed the upper bounds to < the same number as the
=
lower bound in the next statement. Given that there are errors introduced
using floating point numbers, this isn't as far fetched as it may sound.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



I
have doesn't
seem However,

 

[Ken Little]

That worked! I was trying to look up Select Case to find more about it. It
was recommended as a way to speed up your code. It also said that you
should try to put the most likely outcome at the first of the expression.
This makes sense to me as once Select Case hits a true statement, the
expression ends. This may or may not be true as I have not tested it.
However, if this is the case and I rearranged the expression, knowing that
most Scores will be "3" to "6", using the < operator, if PctScore was .803,
wouldn't "Case Is <= 1 be True, if I had it as the first test? The same
with Case Is < .95? The way the expression is structured now, it tests up
to the point and stops. If I put the most likely at the top, there might be
several true statements. Is this correct or am I misunderstanding how the
Select Case statement functions?

Ken
__________________

That should have worked! In looking at it again, though, I realized you
don't need the >= part of the expression. That's part of the beauty of using
Case statements: it goes through them in sequence until it finds one that it
matches.

Public Function AIScore(PctScore As Single) As Integer
Select Case PctScore
Case Is < 0.5
AIScore = 11
Case Is < 0.55
AIScore = 10
Case Is < 0.6
AIScore = 9
Case Is < 0.65
AIScore = 8
Case Is < 0.7
AIScore = 7
Case Is < 0.75
AIScore = 6
Case Is < 0.8
AIScore = 5
Case Is < 0.85
AIScore = 4
Case Is < 0.9
AIScore = 3
Case Is < 0.95
AIScore = 2
Case Is <= 1
AIScore = 1
Case Else
AIScore = 0
End Select

End Function


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Doug,

One last thing. I cut and pasted the code into a query and when

PctScore

=
.803 the code returned a value of "10." The code in the query was: AlScore:
Alscore([PctScore]) . When I run the query, the PctScore calculated value
is .803. Theoretically, I would have thought the AIScore would have

been

a
"4," but it was "10." Any ideas?

Ken

I'm
just ..54999
and In
MS

 

[Douglas J. Steele]

Putting the most likely one at the top is really only appropriate when
you're using SELECT CASE with equalities. If you're going to do something
based on colour, for example, and you know that red is the most common
colour and black the least common, you'd use

SELECT CASE Colour
CASE "Red"
...
CASE "Green"
...
CASE "Blue"
...
CASE "Black"
...
CASE ELSE
...
END SELECT

Of course, if we could figure out why the CASE IS >= 0.8 AND PctScore < 0.85
didn't work as advertised, then you could rearrange them!