Jak dodać warunek do funkcji z null

[Anetal]

Hi,


I have function.

I need to add iff value is null then "no data".
How to do it?

Option Compare Database




Public Function wyznacz(studScore As Single) As String


Select Case studScore
Case 1 To 5
wyznacz = "do 5"
Case 6 To 10
wyznacz = "do 10"
Case Else
wyznacz = "pow 10"
End Select
End Function

[ranman256]

If IsNull(studScore) Then
  wyznacz = "no data"
Else
  Select Case studScore
    Case 1 To 5
       wyznacz = "do 5"
    Case 6 To 10
       wyznacz = "do 10"
    Case Else
       wyznacz = "pow 10"
    End Select
End If

[Anetal]

Thanks, but something doesn't work. I have errors in table:



Option Compare Database


Public Function wyznacz(studScore As Single) As String




If IsNull(studScore) Then
wyznacz = "no data"
Else
Select Case studScore
Case 0 To 5
wyznacz = "do 5"
Case 6 To 10
wyznacz = "do 10"
Case Else
wyznacz = "pow 10"
End Select
End If
End Function

Score	wyznacz
0	do 5
10	do 10
2	do 5
200	pow 10
100	pow 10
	#Błąd
	#Błąd

[orange]

My guess is that a variable declared as Single can not contain a NULL. Probably get Invalid use of NULL error or similar.

[Micron]

That suggests that some Score values are not Null, they are empty strings. You can test for this by creating a test query on your data, like
SELECT * FROM tblYourTable WHERE [Score]="";
or in code, try

If Len(studScore) > 0 Then

EDIT - Never mind. I missed that Score is a number, not text.
In that case, one could declare as a Variant - or fix the data

[davegri]

Maybe this:

If Not IsNumeric(studScore) Then
	wyznacz = "no data"
Else
	Select Case studScore
		Case 0 To 5
			wyznacz = "do 5"
		Case 6 To 10
			wyznacz = "do 10"
		Case Else
			wyznacz = "pow 10"
	End Select
End If
End Function

[orange]

cross posted with responses at https://www.access-programmers.co.uk.../#post-1739927

[Micron]

I agree with Orange - there ought to be an error when trying to pass a null to a variable of any numeric type. The fact that it isn't being reported is odd. I can't get it to not fail with error 94 or 13, even with On Error Resume Next

[Anetal]

It dosesn't work. I added a file.

[Micron]

Since you didn't comment on cross posting (post 7) I imagine you're not going to let anyone know you stated at AWF that this is solved, so I will.

[davegri]

Change the query:

[Anetal]

Thank you very much. This way is better.

[davegri]

Glad to help. Please let us know when you ask same question at other forums. We can check there and see if you already have a solution, and save our time without duplicate effort.