Using a percentage in VBA

[CuriousGeorg]

good afternoon,

I'm working on a user form where quality checks are completed in a system and dumps to Access. I am pulling this information from Access into an Excel userform.

(user restrictions as to why this cant be done in access, so we can skirt around this)

the issue im having is I need a caption to change depending on the score above it. But the score is a percentage? Does this matter when coding?

for example

If qcscore.Caption = "" Thenqcrate.Caption = "n/a"
ElseIf qcscore.Caption < 85 And qcscore.Caption > 0 Then
qcrate.Caption = "Below Expectations"
ElseIf qcscore.Caption < 90 And qcscore.Caption > 85 Then
qcrate.Caption = "Good"
ElseIf qcscore.Caption > 90 And qcscore.Caption < 101 Then
qcrate.Caption = "Outstanding"
End If

Problem I'm getting is even if the user has 100% across the board the caption reads "Below Expectations"
if i change this to

If qcscore.Caption = "" Thenqcrate.Caption = "n/a"
End If
If qcscore.Caption > 0 And qcscore.Caption < 80 Then
qcrate.Caption = "Below Expectations"
End If
If qcscore.Caption > 80 And qcscore.Caption < 85 Then
qcrate.Caption = "Good"
End If
If qcscore.Caption > 85 And qcscore.Caption < 101 Then
qcrate.Caption = "Outstanding"
End If

I only get "outstanding".

Can anyone advise what I'm doing wrong please?

[p45cal]

the Caption property of something is usually a string. If you have the % character in there (a) it won't be able to implicitly convert the string to a number and (b) I'm surprised you don't get a Type mismatch error poppng up; do you have On Error Resume Next or somesuch in action while those lines of code are executing? What kind of control is qscore? It could be that you have to remove the % character before trying to numerically compare the caption:

if replace(qscore.Caption,"%","") >0

As an aside, if you have exactly 80, or exactly 85, qcrate's caption will not change at all since those values fall into no category. Taking just the 80 value, either:
< 80 becomes <= 80
or:
> 80 becomes >= 80
(preferably don't do both!).

[ranman256]

FYI a better IF method is the CASE statement...esp. for readability:

Select Case True
    Case qcscore.Caption = ""
        qcrate.Caption = "n/a"
    Case qcscore.Caption > 0 And qcscore.Caption < 80
        qcrate.Caption = "Below Expectations"
    Case qcscore.Caption > 80 And qcscore.Caption < 85
        qcrate.Caption = "Good"
    Case qcscore.Caption > 85 And qcscore.Caption < 101
    qcrate.Caption = "Outstanding"
End Select

[CuriousGeorg]

only just picked up these replies to thanks. I did notice the exact 80 score etc, this was just a template to get it working before i got the official targets in to ensure it was working.

All the qcscore is a % number pulled back from the access database. It is input from another external system (to which I have no way to control), I can change the label to a textbox if this will make it anyway easier. I just used label as it is easier to restrict user change.
I'll give your if replace idea a go then come back with results. (or try text box) thanks.

side note: thanks ranman for that. never sued case before

[p45cal]

(or try text box)

Don't bother, it'll have the same problem of the .Value/.Text (it has no .Caption) not being coerced ino a number if it has a % character in it.