Solved

Between values in a SELECT CASE vba statement

Posted on 2013-01-17
5
732 Views
Last Modified: 2013-01-17
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
Comment
Question by:ssmith94015
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38789073
(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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38789125
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
 
LVL 30

Expert Comment

by:hnasr
ID: 38789131
Upload a sample database.
Type the expected output.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38789196
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
 

Author Closing Comment

by:ssmith94015
ID: 38789508
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now