• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

Between values in a SELECT CASE vba statement

Have an ACCESS 2003 function taht, based on the value passed in, should return a string.  However, I think it is not recognizing the between statement.

Sandra

Select Case varScore
    Case  0 To 1.749
        fntRankings = "Unacceptable"
    Case 1.74901 To 2.999
        fntRankings "Does Not Meet"
    Case 3# To 3.749
        fntRankings "Meets"
    Case 3.7491 To 4.249
        fntRankings "Meets Plus"
    Case 4.2491 To 4.749
        fntRankings "Exceeeds"
    Case 4.7491 To 5#
        fntRankings "Outstanding"
    Case Else
         fntRankings "Unknown"
End Select
0
ssmith94015
Asked:
ssmith94015
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(1)  Is the # a typo?
(2)  What does the fnt stand for?   In one line (0 To 1.749) you're setting it to a string, in others you have a string as a parameter, which means it's not setting it to that string.
0
 
Dale FyeCommented:
You might want to consider putting these values in a table, rather than code.  That would facilitate the use of a DLOOKUP.  The table would look like:

LTE          GT            Ranking
0             1.749         Unacceptable
1.749      2.999         Does Not Meet
2.999      3.749         Meets
3.749      4.249         Meets Plus
4.249      4.749         Exceeeds
4.749      5.0             Outstanding
5.0          NULL         Unknown

Then, you could use a DLOOKUP like:

=DLOOKUP("Ranking", "YourTable", "[LTE] <= " & TestValue & " AND [GT] > " & TestValue)

This method provides you with the flexibility of modifying your upper and lower values associated with each ranking without having to rewrite your code.
0
 
hnasrCommented:
Upload a sample database.
Type the expected output.
0
 
Rey Obrero (Capricorn1)Commented:
how did you declare varScore ?
it should be declared as double to recognize the decimal values


dim varScore as double

or in your function

function whatever(varScore as double)
0
 
ssmith94015Author Commented:
fntRanking is actually the function name and there is already a table which I can now capitalize on.  Thank you for pointing that out!

The # sign was for whatever reason added when I ran the function, don't know why.

Sandra
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now