Retuning null from a function? - Howard

I have a function used in my queries that takes two single character string
parameters representing examination gardes.
If the two characters are in the range A to G my function returns the
difference between them. i.e. how many grades separate the two parameters.
Trouble is, in the fields used as parameters there is somtimes a letters X
or U, which are not real grades and sometimes a null if one of the grades is
missing.

In these three circumstances I'd like my function to return null as the
comparison has no meaning.

How can I do this?

The function  at present is...
....................................................................................................
Public function GradeComp(G1 as string, G2 as string) as integer

Dim G1value as integer
Dim G2value as integer

G1Value = GradeToInt(G1)   ' use the UDF GradeToInt function to convert
the grade to its points value
G2Value = GradeToInt(G2)   ditto
GradeComp = G2Value - G1Value   'return the difference in points
end function
..............................................................................................

What I want is sonething like the following algorithm

if Valid Grade (g1) and Valid Grade (G2) then
calculate and return grade difference
else
return null
end if

Retuning null from a function? - Douglas J. Steele

The only data type that can handle Null is Variant, so you'd have to change
your declaration to

Public function GradeComp(G1 as string, G2 as string) as Variant

Assuming you have a ValidGrade function that returns True or False, you
could then use:

Public function GradeComp(G1 as string, G2 as string) as integer

Dim G1value as integer
Dim G2value as integer

If ValidGrade(G1) And ValidGrade (G2) Then
G1Value = GradeToInt(G1)
G2Value = GradeToInt(G2)
GradeComp = G2Value - G1Value
Else
GradeComp = Null
End If

End Function


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Retuning null from a function? - John Spencer

IF the grades you are passing in can be null then you need to use
Variants for G1 and G2.  If you don't passing in Null will error.

If you want to return either an integer or Null you have to define the
result as null.  Again if you try to set a string variable to null you
will get an error.

That said you should be able to test G1 and G2 to see if they meet the
specified values and then execute your code or set the return value to null.

Public function GradeComp(G1 as Variant, G2 as Variant) as Variant

Dim G1value as integer
Dim G2value as integer

If G1 >= "A" and G1 <= "G" and G2 >= "A" and G2 <= "G" THEN
G1Value = GradeToInt(G1)   ' use the UDF GradeToInt function
G2Value = GradeToInt(G2)   ditto
GradeComp = G2Value - G1Value   'return the difference in points
Else
GradeComp = Null
End If

end function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Retuning null from a function? - Howard

Thank you Douglas, just what I neded

Howard

Retuning null from a function? - Howard

Thanks John, I can see why I need to protect my GradeToInt function as well

Howard