Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

DLookup vs. query result

Discussion in 'Information Technology' started by EJO, Jul 28, 2006.

  1. EJO

    EJO
    Expand Collapse
    Guest

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

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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" <MyD0j0@gmail.com> wrote in message
    news:1151340556.438381.302530@r2g2000cwb.googlegroups.com...
    > 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
     
  4. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

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

    EJO
    Expand Collapse
    Guest


    > 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.
     
  6. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    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
     
  7. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    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" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:%23H%23keIUmGHA.2372@TK2MSFTNGP04.phx.gbl...
    > 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" <MyD0j0@gmail.com> wrote in message
    > news:1151340556.438381.302530@r2g2000cwb.googlegroups.com...
    >> 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

    >
    >
     
  8. EJO

    EJO
    Expand Collapse
    Guest

    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!
     

Share This Page