DLookUp Invalid Use of NUll

  • Thread starter Thread starter iholder
  • Start date Start date
I

iholder

I have read previous post on this error, but I am still having a problem.

The Box No. field is text data. I am trying to check if the Box No is on
file, before enter a new Box Number.

The DLookup string is return and "Invalid Use of Null"

Is there another approach to resolving this problem. See problem code below.


Dim strBoxNo
Dim Result As Variant
strBoxNo = InputBox("Enter Box Number")
' MsgBox strBoxNo
Result = DLookup([BoxNo], "tblRecordsMgt", "[Box No]=" & [strBoxNo])
If IsNull(Result) Then MsgBox "Not on File"


Thank you
 
Hi iholder,

correct sintax for your statement is:

Result = DLookup("[Box No]", "tblRecordsMgt", "[Box No]=""" & strBoxNo & """")

I dunno if you've a spece between box and no 'cause once you put it and once
not.
In the statement I wrote I put the space.
Check it and put the correct name.

HTH Paolo
 
If Box No is text then you need apostrophies and also you can combine the
check without having to define a result variable
If IsNull(DLookup("[BoxNo]", "tblRecordsMgt", "[BoxNo] = '" & [strBoxNo] &
"'")) Then
MsgBox "Not on File"
End if
 
I think the problem is that when DLookup returns Null, you're trying to
assign a Null value to the Result field, and VBA doesn't like that. I thin
you need to use the Nz function to return an actual value in case of Null,
e.g.

Result = Nz(DLookup([BoxNo], "tblRecordsMgt", "[Box No]=" & [strBoxNo]),-1)

I put -1 in there, but you can use any value that you know would never be
returned. Then check for that value instead of chacking for Null.
 
Thank you for your help. The syntax still needing a minor change. For future
reference anyone reading this post. I needed to use single quotes and doubles

corrected
Result = DLookup("[BoxNo]", "tblRecordsMgt", "[BoxNo]=' " & strBoxNo & " '
")


Paolo said:
Hi iholder,

correct sintax for your statement is:

Result = DLookup("[Box No]", "tblRecordsMgt", "[Box No]=""" & strBoxNo & """")

I dunno if you've a spece between box and no 'cause once you put it and once
not.
In the statement I wrote I put the space.
Check it and put the correct name.

HTH Paolo

iholder said:
I have read previous post on this error, but I am still having a problem.

The Box No. field is text data. I am trying to check if the Box No is on
file, before enter a new Box Number.

The DLookup string is return and "Invalid Use of Null"

Is there another approach to resolving this problem. See problem code below.


Dim strBoxNo
Dim Result As Variant
strBoxNo = InputBox("Enter Box Number")
' MsgBox strBoxNo
Result = DLookup([BoxNo], "tblRecordsMgt", "[Box No]=" & [strBoxNo])
If IsNull(Result) Then MsgBox "Not on File"


Thank you
Click to expand...
Click to expand...
 
Thank you
I needed to use single quotes and doubles in the syntax. Now it works fine.



Dennis said:
If Box No is text then you need apostrophies and also you can combine the
check without having to define a result variable
If IsNull(DLookup("[BoxNo]", "tblRecordsMgt", "[BoxNo] = '" & [strBoxNo] &
"'")) Then
MsgBox "Not on File"
End if


iholder said:
I have read previous post on this error, but I am still having a problem.

The Box No. field is text data. I am trying to check if the Box No is on
file, before enter a new Box Number.

The DLookup string is return and "Invalid Use of Null"

Is there another approach to resolving this problem. See problem code below.


Dim strBoxNo
Dim Result As Variant
strBoxNo = InputBox("Enter Box Number")
' MsgBox strBoxNo
Result = DLookup([BoxNo], "tblRecordsMgt", "[Box No]=" & [strBoxNo])
If IsNull(Result) Then MsgBox "Not on File"


Thank you
Click to expand...
Click to expand...
 
Not correct. Result is Dimmed as a Variant. Variant variables will accecpt
Null values. Variant is the only type of memory varialbe that will do so.
It is actually property to use a Variant to receive the results of a DLookup
to avoid the Invalid use of Null. In this case, it had to do with a data
type mismatch. Why is said it was an invalid use of Null, I don't know.
--
Dave Hargis, Microsoft Access MVP


Jim Burke in Novi said:
I think the problem is that when DLookup returns Null, you're trying to
assign a Null value to the Result field, and VBA doesn't like that. I thin
you need to use the Nz function to return an actual value in case of Null,
e.g.

Result = Nz(DLookup([BoxNo], "tblRecordsMgt", "[Box No]=" & [strBoxNo]),-1)

I put -1 in there, but you can use any value that you know would never be
returned. Then check for that value instead of chacking for Null.

iholder said:
I have read previous post on this error, but I am still having a problem.

The Box No. field is text data. I am trying to check if the Box No is on
file, before enter a new Box Number.

The DLookup string is return and "Invalid Use of Null"

Is there another approach to resolving this problem. See problem code below.


Dim strBoxNo
Dim Result As Variant
strBoxNo = InputBox("Enter Box Number")
' MsgBox strBoxNo
Result = DLookup([BoxNo], "tblRecordsMgt", "[Box No]=" & [strBoxNo])
If IsNull(Result) Then MsgBox "Not on File"


Thank you
Click to expand...
Click to expand...
 
Actually, you don't have to use single quotes. In fact, it is better to use
doulbe quotes because in some cases, particularly with names, there can be a
single qoute in the data that will cause an error. The way you embedd double
qoutes in a string is to use two double qoutes ""
Now, getting the correct number of double qoutes to get the syntax correct
used to really confuse me. I could never get it right. But, I found a
mental trick that makes it really very easy. What I used to do (until it
became natural) was to first write the code using single qoutes:
If IsNull(DLookup("[BoxNo]", "tblRecordsMgt", "[BoxNo] = '" & [strBoxNo] &
"'"))

Then go back and replace each single qoutes with two double quotes:
If IsNull(DLookup("[BoxNo]", "tblRecordsMgt", "[BoxNo] = """ & [strBoxNo] &
""""))
--
Dave Hargis, Microsoft Access MVP


iholder said:
Thank you
I needed to use single quotes and doubles in the syntax. Now it works fine.



Dennis said:
If Box No is text then you need apostrophies and also you can combine the
check without having to define a result variable
If IsNull(DLookup("[BoxNo]", "tblRecordsMgt", "[BoxNo] = '" & [strBoxNo] &
"'")) Then
MsgBox "Not on File"
End if


iholder said:
I have read previous post on this error, but I am still having a problem.

The Box No. field is text data. I am trying to check if the Box No is on
file, before enter a new Box Number.

The DLookup string is return and "Invalid Use of Null"

Is there another approach to resolving this problem. See problem code below.


Dim strBoxNo
Dim Result As Variant
strBoxNo = InputBox("Enter Box Number")
' MsgBox strBoxNo
Result = DLookup([BoxNo], "tblRecordsMgt", "[Box No]=" & [strBoxNo])
If IsNull(Result) Then MsgBox "Not on File"


Thank you
Click to expand...
Click to expand...
Click to expand...
 
My mistake. I didn't catch the dim statement.

Klatuu said:
Not correct. Result is Dimmed as a Variant. Variant variables will accecpt
Null values. Variant is the only type of memory varialbe that will do so.
It is actually property to use a Variant to receive the results of a DLookup
to avoid the Invalid use of Null. In this case, it had to do with a data
type mismatch. Why is said it was an invalid use of Null, I don't know.
--
Dave Hargis, Microsoft Access MVP


Jim Burke in Novi said:
I think the problem is that when DLookup returns Null, you're trying to
assign a Null value to the Result field, and VBA doesn't like that. I thin
you need to use the Nz function to return an actual value in case of Null,
e.g.

Result = Nz(DLookup([BoxNo], "tblRecordsMgt", "[Box No]=" & [strBoxNo]),-1)

I put -1 in there, but you can use any value that you know would never be
returned. Then check for that value instead of chacking for Null.

iholder said:
I have read previous post on this error, but I am still having a problem.

The Box No. field is text data. I am trying to check if the Box No is on
file, before enter a new Box Number.

The DLookup string is return and "Invalid Use of Null"

Is there another approach to resolving this problem. See problem code below.


Dim strBoxNo
Dim Result As Variant
strBoxNo = InputBox("Enter Box Number")
' MsgBox strBoxNo
Result = DLookup([BoxNo], "tblRecordsMgt", "[Box No]=" & [strBoxNo])
If IsNull(Result) Then MsgBox "Not on File"


Thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top