invalid use of null

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

Hi,

I trying to check to see if a dlookup return a null response, and enter a
default,

StrProjectManager = DLookup("Contact_Name", "Contact Types Register
Query", "Contact_Type = 'Project Manager' AND Project_ID = " & IntProjectID)
If StrProjectManager = "" Then
StrProjectManager = "Not Available"
End If

but this results in and error "Invalid use of Null"

Any suggestions welcome.
 
Greg said:
Hi,

I trying to check to see if a dlookup return a null response, and enter a
default,

StrProjectManager = DLookup("Contact_Name", "Contact Types Register
Query", "Contact_Type = 'Project Manager' AND Project_ID = " &
IntProjectID)
If StrProjectManager = "" Then
StrProjectManager = "Not Available"
End If

but this results in and error "Invalid use of Null"

Any suggestions welcome.
Click to expand...

Try surrounding the Dlookup stuff with the Nz function (transforms a Null
into a zero length string):

StrProjectManager = Nz(DLookup("Contact_Name", "Contact Types Register
Query", "Contact_Type = 'Project Manager' AND Project_ID = " &
IntProjectID))
 
On Tue, 24 Feb 2009 19:16:01 -0800, Greg <Greg
(e-mail address removed)> wrote:

I'm guessing StrProjectManager is a string, and strings cannot be
null. Use a variant, or restructure your code.

-Tom.
Microsoft Access MVP
 
Hi Greg,
the error suggests that Project Manager is Null or Project_ID is Null.
You can use the following:

If Len(Me.[Project Manager]) >0 And Len(IntProjectID)) >0 Then
StrProjectManager = DLookup("Contact_Name", "Contact Types Register
Query", "Contact_Type = 'Project Manager' AND Project_ID = " &
IntProjectID)
If StrProjectManager = "" Then
StrProjectManager = "Not Available"
End If
Else
StrProjectManager = "Not Available"
End If

Note: the above code first checks that Project Manager is not Null or
IntProjectiD is not Null. If either of them is Null, then it doesn't try to
workout the value for StrProjectManager.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
On Tue, 24 Feb 2009 19:16:01 -0800, Greg <Greg
Hi,

I trying to check to see if a dlookup return a null response, and enter a
default,

StrProjectManager = DLookup("Contact_Name", "Contact Types Register
Query", "Contact_Type = 'Project Manager' AND Project_ID = " & IntProjectID)
If StrProjectManager = "" Then
StrProjectManager = "Not Available"
End If

but this results in and error "Invalid use of Null"

Any suggestions welcome.
Click to expand...

DLookUp will return a NULL (not a zero length string) if there is no match.
I'd suggest using the NZ() function to return your value:

Dim vProjectManager As String
strProjectManager = NZ(DLookup("Contact_Name", _
"Contact Types Register Query", _
"Contact_Type = 'Project Manager' AND Project_ID = " & IntProjectID), _
"Not Available")
 
Back
Top