DLookup vs. query result

[EJO]

Hello and thanks!

The following code is causing the error 'invalid use of null', but if I
use the same parmeters in a regular query, the correct ID is pulled,
how can I correct for the difference?


Dim intCktID As Integer, intSiteID As Integer, strCktID As String

intSiteID = Forms!Site!Text2
strCktID = Me.CktID

intCktID = DLookup("[ID]", "CktsList", "[Site_ID]= " & intSite & " AND
[Display]=' " & strCktID & " ' ")

DoCmd.OpenForm "CktModify", , , "[ID] = " & intCktID

 

[Allen Browne]

It appears you have extra spaces inside the quotes for the 2nd phrase of the
3rd argument. This could mean that no ID is found, so the result of
DLookup() is null, and the attempt to assign Null to the integer variable
results in Error 94 (invalid use of Null).

Try:
Dim varChkID As Variant

varCktID = DLookup("[ID]", "CktsList", _
"([Site_ID]= " & intSite & ") AND ([Display]= """ & strCktID & """)")
If IsNull(varCktID) Then
MsgBox "Not found"
Else
DoCmd.OpenForm "CktModify", , , "[ID] = " & varCktID
End If

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Rick Brandt]

Hello and thanks!

The following code is causing the error 'invalid use of null', but if
I use the same parmeters in a regular query, the correct ID is pulled,
how can I correct for the difference?


Dim intCktID As Integer, intSiteID As Integer, strCktID As String

intSiteID = Forms!Site!Text2
strCktID = Me.CktID

intCktID = DLookup("[ID]", "CktsList", "[Site_ID]= " & intSite & " AND
[Display]=' " & strCktID & " ' ")

DoCmd.OpenForm "CktModify", , , "[ID] = " & intCktID

I would guess that the space after your first single quote and before your
last single quote should not be there. That will literally look for a space
followed by your ID followed by another space.

People sometimes write code examples like that so that the single quote can
be easily seen, but you don't want those spaces in your actual code.

 

[EJO]

I would guess that the space after your first single quote and before your
last single quote should not be there. That will literally look for a space
followed by your ID followed by another space.

People sometimes write code examples like that so that the single quote can
be easily seen, but you don't want those spaces in your actual code.

I intentionally put the spaces there for the intent of the post for the
reason you state; i've seen in other posts where that is an issue with
string criteria.

 

[Rick Brandt]

I intentionally put the spaces there for the intent of the post for
the reason you state; i've seen in other posts where that is an issue
with string criteria.

All I can suggest then is to put that into the immediate window and break it
into poieces ot see what you get. Try it once each with only one of the
criteria to see what you get.

 

[Pat Hartman\(MVP\)]

Nice article Allen. My solution is a little more of a hammer. I add a
constant to each database.

Const QUOTE = """"
Four quotes = the two outside quotes plus the double inside to store a
single = "--""--"
I think this makes the statement a little easier to read since you never
have to see multiple quotes. You just concatenate the QUOTE constant
whenever you want to put a quote inside a string.

varCktID = DLookup("[ID]", "CktsList", _
"[Site_ID]= " & intSite & " AND [Display]= " & QUOTE & strCktID & QUOTE)

It appears you have extra spaces inside the quotes for the 2nd phrase of
the 3rd argument. This could mean that no ID is found, so the result of
DLookup() is null, and the attempt to assign Null to the integer variable
results in Error 94 (invalid use of Null).

Try:
Dim varChkID As Variant

varCktID = DLookup("[ID]", "CktsList", _
"([Site_ID]= " & intSite & ") AND ([Display]= """ & strCktID & """)")
If IsNull(varCktID) Then
MsgBox "Not found"
Else
DoCmd.OpenForm "CktModify", , , "[ID] = " & varCktID
End If

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello and thanks!

The following code is causing the error 'invalid use of null', but if I
use the same parmeters in a regular query, the correct ID is pulled,
how can I correct for the difference?


Dim intCktID As Integer, intSiteID As Integer, strCktID As String

intSiteID = Forms!Site!Text2
strCktID = Me.CktID

intCktID = DLookup("[ID]", "CktsList", "[Site_ID]= " & intSite & " AND
[Display]=' " & strCktID & " ' ")

DoCmd.OpenForm "CktModify", , , "[ID] = " & intCktID

 

[EJO]

It appears you have extra spaces inside the quotes for the 2nd phrase of the
3rd argument. This could mean that no ID is found, so the result of
DLookup() is null, and the attempt to assign Null to the integer variable
results in Error 94 (invalid use of Null).

Nope. You guys were right...

In double checking to make sure I had the single quotes in there, i did
in fact unintentionally leave spaces in there. Thanks, I hope not to
waste your time on my inability to be more thorough any longer!