How to add null handling condition in function

Anetal

New member
Local time
Today, 10:18
Joined
Jan 4, 2021
Messages
10
Hi,

I have function. How correctly add condition: case isnull then "no data"?


Option Compare Database

Public Function AssignGrade(studScore As Single) As String

Select Case studScore
Case 0 To 100
AssignGrade = "A"
Case 101 To 200
AssignGrade = "B"
Case 201 To 1000
AssignGrade = "C"
Case Else
AssignGrade = "F"
End Select
End Function
 
Last edited:
Hi. What grade would you assign for null or no data?
 
Change it to Select Case Nz(studScore,0) then add Case 0 with AssignGrade "n/d"
 
Change it to Select Case Nz(studScore,0) then add Case 0 with AssignGrade "n/d"
Something doesn't work.

For Score 0 I need Grade A
For score null I need Grade "b/d"



Kwerenda1 Kwerenda1

ScoreGrade
0​
A
10​
A
2​
A
200​
B
100​
A
#Error
#Error
 
To do this, add NZ(studScore, -1) and then add a case for that. NZ can substitute anything; it is not limited to 0 or "" as a substitution value.
 
To do this, add NZ(studScore, -1) and then add a case for that. NZ can substitute anything; it is not limited to 0 or "" as a substitution value.

I change this part and still doesn't work:



ScoreGrade
0​
A
10​
A
2​
A
200​
B
100​
A
#Error
#Error
10​
A



Option Compare Database

Public Function AssignGrade(studScore As Single) As String

Select Case Nz(studScore, -1)
Case -1
AssignGrade = "n/d"
Case 0 To 100
AssignGrade = "A"
Case 101 To 200
AssignGrade = "B"
Case 201 To 1000
AssignGrade = "C"
Case Else
AssignGrade = "F"
End Select
End Function
 
I used 0 in the suggested Nz expression as you hadn't included it in your original post...which you edited after I replied.
It will work with -1 with the following changes:

Code:
Public Function AssignGrade(studScore As Integer) As String

Dim StudentScore As Integer
StudentScore = Nz(studScore, -1)

Select Case StudentScore
Case -1
AssignGrade = "n/d"
Case 0 To 100
AssignGrade = "A"
Case 101 To 200
AssignGrade = "B"
Case 201 To 1000
AssignGrade = "C"
Case Else
AssignGrade = "F"
End Select

End Function

Query based on that
Code:
SELECT Table1.ID, Table1.StudScore, AssignGrade(Nz([StudScore],-1)) AS Grade
FROM Table1;

Sample Results:
1609772876255.png
 
@Anetal, @theDBguy
Two comments on the databases you both uploaded
1. The module has 2 lines missing at the start:
Code:
Option Compare Database
Option Explicit
2. The change made by theDBGuy was to use Variant datatype instead of Integer.
Unless absolutely necessary, I avoid using variants
Whilst I can see that worked, I had no issue using integer datatype in my tests before post #9

However the main reason I'm replying is that your database exposed what may be a new bug in Access 365
I have A365 version 2011 Build 16.0.13426.20270 32-bit

When I tried to run the query, the status bar showed the message "The action or event has been blocked by Disabled Mode"
It transpired that the yellow security bar is no longer appearing for me in A365 so I wasn't aware it wasn't trusted.
The security bar appears as usual in A2010
 
Code:
Public Function AssignGrade(studScore As Single) As String

needs to be

Code:
Public Function AssignGrade(studScore As Variant) As String

Only Variants can contain null values.
 
Yes I'm aware of that.
Nevertheless the function & query I gave in post #9 works perfectly.
Attached is the test db I used at the time
 

Attachments

Further to my comments about the missing security bar in A365, I tried an Office repair but it made no difference.
I've now reinstalled Office 365 from scratch and now have Version 2011 Build 13426.20404 32-bit.
There is still no security bar visible. Any suggested fixes appreciated
 
needs to be

Code:
Public Function AssignGrade(studScore As Variant) As String

Only Variants can contain null values.
The "advantage" of being able to pass a Null is more than offset by the huge disadvantages of allowing literally anything to be passed to the function.

Even an object can be passed as a Variant so an argument referring to a control may be taken either as the Control object itself or its Value Property. Make one reference in the function to the parameter in a way that would mean it is a control and what was thought as being passed as the Value becomes the control itself. Even if declared ByVal this is overridden as ByRef (as it is with all objects). Now the value in the control the programmer thought was being passed ByVal can potentially be updated back though the call.

The nature of the function parameters becomes obscure because it cannot determined by the definition. Just horrible programming practice and aught to be avoided by all.

For this reason it is very unusual to pass a Variant to a function. The functions built into VBA specify a type and the programmer needs to handle the Nulls in the call, such as is shown by Isladogs in Post #9. (My preference is to avoid calling the function completely when Nulls are encountered rather than passing an out of scope value.)
 

Users who are viewing this thread

Back
Top Bottom