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

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EJO" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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

EJO wrote:
>> 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.

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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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)

"Allen Browne" <(E-Mail Removed)> wrote in message
news:%23H%(E-Mail Removed)...
> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "EJO" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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 wrote:
> 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!