DLookUp Invalid Use of NUll

[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

 

[Paolo]

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

 

[Dennis]

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

 

[Jim Burke in Novi]

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]

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 & " '
")

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

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

 

[iholder]

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

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 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

 

[Klatuu]

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

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.

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

 

[Klatuu]

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

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

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 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

 

[Jim Burke in Novi]

My mistake. I didn't catch the dim statement.

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

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.

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