Case Is "Blank"

[n107jkx]

I have a number of different cases in a module, which each bring up different values for a field in a query. However, i need to bring up a special value when the "case field" is blank.

I am sure there will be an easy answer, but I have tried Case Is = "" and this does not work.

Can anybody help?

 

[TryingToLearn]

haven't tested, but try VBnullString

 

[n107jkx]

This is my module:

Function GetScores(dblScoreIn As Double)
Select Case dblScoreIn
Case Is >= 80
GetScores = "A"
Case Is >= 70
GetScores = "B"
Case Is >= 60
GetScores = "C"
Case Is >= 50
GetScores = "D"
Case Is >= 40
GetScores = "E"
-----------------------------
GetScores = "Abs"
Case Else
GetScores = "U"
End Select
End Function

I need the case when the field is blank to appear where the line of hyphens is.

vbNullString doesn't seem to work in any of the senses that i tried it...??

 

[Joe4]

I believe it is because you are dimensioning dblScoreIn as a numeric value (Double). In numeric terms, blank is the same as zero, and Access cannot differentiate the two.

I think you want to dimension your input value as a String, then convert it to a number (if not blank) within your function. That way you can "capture" the blank field.

Here is how it would be done in Excel. Access show be very similar (I am not in front of Access right now to test it). At the very least, the concept would be the same:

Function GetScores(strScoreIn As String) As String

    Dim dblScoreIn As Double
    If IsNumeric(strScoreIn) Then
        dblScoreIn = strScoreIn
        Select Case dblScoreIn
            Case Is >= 80
                GetScores = "A"
            Case Is >= 70
                GetScores = "B"
            Case Is >= 60
                GetScores = "C"
            Case Is >= 50
                GetScores = "D"
            Case Is >= 40
                GetScores = "E"
            Case Else
                GetScores = "U"
        End Select
    Else
        GetScores = "Abs"
    End If
    
End Function

 

[n107jkx]

This does not seem to work, although i do not know why - the code seems fine...

I have come around the problem by creating two queries, one to find null scores and set the grade as "Abs", and another to find Not Null scores and apply the GetScores function to attach a suitable grade.

The only problem i have now is combining the two queries back together again into one table of scores.

 

[Joe4]

As I mentioned, the code I wrote was written in Excel VBA, not Access VBA. I didn't have time to create a database to write it in Access VBA. Access VBA may use a few different command and keywords, but the concept and theory are the same.

The important point I was trying to make was you were inputting the variable as a Numeric value. If you want to check for the Null value or a zero length entry, input the variable as a String. You can then convert it to a number within the Function after you check the length of it.

 

[Joe4]

I tried recreating your problem, and think that I see the issue. I don't think that it is even getting to the code for the blank entries, so it appears that what we are trying to put in the code is having no affect. It is erroring because the function expects something to be input, and if nothing is input, it is bombing out.

I am not sure how to work around it code wise, but we may be able to tackle the problem from a different angle. If the "dblScoreIn" is coming from a table, we can set the Required property on the "dblScoreIn" field to "Yes". This will require the user to enter something when entering the data.

If the data is being imported, I would write a query and create a new calculated field like:

NewdblScoreIn: IIf(IsNull([dblScoreIn]),999,[dblScoreIn])

Then write your function like this:

Function GetScores(NewdblScoreIn As Double)

    Select Case NewdblScoreIn
        Case Is = 999
            GetScores = "Abs"
        Case Is >= 80
            GetScores = "A"
        Case Is >= 70
            GetScores = "B"
        Case Is >= 60
            GetScores = "C"
        Case Is >= 50
            GetScores = "D"
        Case Is >= 40
            GetScores = "E"
        Case Else
            GetScores = "U"
    End Select

End Function

So, in your query, you will input the calculated filed (NewdblScoreIn) instead of dblScoreIn. I tested it out and it works.

 

[n107jkx]

The dblScoreIn is not coming from a table. I have set up a percentage field in a query to calculate a percentage from the mark divided by the maximum mark. This will be blank if the mark field is blank (i.e the work has not been completed) and this is the field that the grade is calculated from using my function.

 

[Joe4]

Then did you try the second method I proposed? If it is being calculated in a query, you can control the value. So using an IIF statement, instead of returning a blank, return "999" if the field used in the calculation is blank and then use the code provided.

 

[n107jkx]

Lovely! It works a treat. I used -1 instead of 999, like on Champ Man - if you are familiar with that, which you probably aren't if ur in the US of A!

Thanks for all ur help



Go Rams!