Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup Syntax with 2 criteria (2000)

    What's the correct syntax for the DLookup function when you have 2 criteria to check? I thought it was something like this:

    Expr1: DLookUp("[Description]","Facility Lookup Table","[Facility]=TSM" And "[Outpost]=MANDAN")

    But that doesn't work.
    What's wrong with that?

    Sarah

  2. #2
    New Lounger
    Join Date
    Jan 2003
    Location
    USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup Syntax with 2 criteria (2000)

    I think you need to remove the quotes around the '" and " so it is one long string.

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: DLookup Syntax with 2 criteria (2000)

    Could you please post the correct code so others can benefit. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  4. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup Syntax with 2 criteria (2000)

    Sure! Here's the corrected code.

    Expr1: DLookUp("[Description]","Facility Lookup Table","[Facility] = 'TSM' " & "AND [Outpost] = 'MANDAN' ")

    Since the criteria was text, single quotes were needed around TSM and MANDAN. Also the ampersand was needed between the 2 criteria strings. And the AND word needed to be included the double quotes.

    I ended up using the DLookup function in VBA code instead of in a query column.

    Sarah

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: DLookup Syntax with 2 criteria (2000)

    <hr>I ended up using the DLookup function in VBA code instead of in a query column.<hr>
    Just a note to add that if you are doing this in a query, it is much faster to use a join (presuming that the lookup table is indexed), and the syntax of the query is easier as well. For single record purposes that I presume the VBA is doing, the DLookup doesn't add noticeable overhead, but when you are returning lots of records, the difference is dramatic.
    Wendell

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: DLookup Syntax with 2 criteria (2000)

    Your statement:
    Expr1: DLookUp("[Description]","Facility Lookup Table","[Facility] = 'TSM' " & "AND [Outpost] = 'MANDAN' ")

    could be further simplified to:
    Expr1: DLookUp("[Description]","Facility Lookup Table","[Facility] = 'TSM' AND [Outpost] = 'MANDAN' ")

    Pat

  7. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup Syntax with 2 criteria (2000)

    <P ID="edit" class=small>(Edited by charlotte on 25-Apr-03 19:11. to activate link)</P>Never mind. I found the correct syntax on The Access Web.

    http://www.mvps.org/access/general/gen0018.htm

    Thanks to those who viewed my question!
    Sarah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •