Run-time error '94': Invalid use of Null

[hoke]

I get the following error when I use dlookup

Run-time error '94': Invalid use of Null

When i do Test(1) a record is found and there is no
problem.
When i do Test(2) no records are found and it results in
error 94.

This problem began when I added the column isDefault with
datatype yes/no to a table
and added " AND isDefault=-1" to the criteria of Dlookup

Can anybody tell me why this happens and give me a
solution to this problem?

thanx in advance

Below you find the function and table definitions.

### Function Test94 ###
Function Test94(pCategoryId) As String
Dim criteria As String

'find the default value for the given category
criteria = "category_id=" & pCategoryId & " AND
isDefault=-1"
Test94 = DLookup("[id]", "lookupvalues", criteria)

End Function

### Table definition lookupvalues ###
lookupvalues
(id autonumber primary key
,category_id number
,value text
,isDefault yes/no
)

### Table data ###
id category_id value isDefault
1 1 "ok" Yes

 

[Scott McDaniel]

DLookup will return a Null value if no record is found. Change your function
like this:

Function Test94(pCategoryId As Variant) As String
Dim varResults As Variant

'find the default value for the given category
varResults = DLookup("[id]", "lookupvalues", "category_id=" &
pCategoryId & " AND isDefault=-1"

If Not Isnull(varResults) Then
Test94 = cstr(varResults)
Else
Test94 = "" 'empty string
End If

End Function

Of course, you may not want to return an empty string ... you may wish to
actually return a null value, in which case you'd have to declare the return
value of your function to be a Variant, and just set the value = varResults.

 

[Guest]

thanx this works