Help with Function Code

  • Thread starter Thread starter Ken Little
  • Start date Start date
K

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
 
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
 
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,

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
 
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
 
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)



Ken Little said:
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 said:
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,
Click to expand...
Click to expand...
 
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
__________________
Douglas J. Steele said:
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)



Ken Little said:
Doug,

One last thing. I cut and pasted the code into a query and when
Click to expand...
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
Click to expand...
been
a
"4," but it was "10." Any ideas?

Ken

I'm
just ..54999
and In
MS
Click to expand...
Click to expand...
 
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!
 
Back
Top