Results 1 to 13 of 13
  1. #1
    NewProgramPerson is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    8

    Issue with DLookup

    Hi:
    I am having a hard time getting DLookup to work. I enter =DLookUp("Lens Type",[Start New Plate]) into the control source of a form, because I want to return a Lens type from the Start New Plate table. I get #Name? returned. I've tried changing the brackets and quotation marks, but I still get the same error. What am I missing? As you've guessed, I'm not a programmer, so I guess I'm missing something basic.


    Thank you

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Dlookup("[Lens Type]","[Start New Plate]")
    All the arguments need to be in quotes. Object names containing spaces need brackets.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Is there only one Record in [Start New Plate]? If not, where is your Criteria portion of the Function?

    If you have multiple Records in [Start New Plate] and don't use a Criteria (aka a Where statement) DLookup() will return a random value from a random Record.

    Or is that the idea?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    NewProgramPerson is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    8

    It Worked!!!!

    Quote Originally Posted by davegri View Post
    Dlookup("[Lens Type]","[Start New Plate]")
    All the arguments need to be in quotes. Object names containing spaces need brackets.

    Awesome. Worked like a champ.
    Thank you

  5. #5
    NewProgramPerson is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    8
    Hi:
    One more question: I added the criteria at the end, so the statement is now: =DLookUp("[Process Flow:]","[Start New Plate]","[Plate Number:]"="[Plate No:]"). I'm want to return the Process Flow: from the Start New Plate table, where the Plate Number: in the Start New Plate table equals the Plate No: in my table. What's wrong with the criteria?
    Thanks

  6. #6
    NewProgramPerson is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    8
    And one more question: Where is a good source for this information on the DLookup structure (and other Access commands)? If you have any advice here, it would be appreciated.
    Thanks again,

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I'm want to return the Process Flow: from the Start New Plate table, where the Plate Number: in the Start New Plate table equals the Plate No: in my table. What's wrong with the criteria?
    equals the Plate No: in my table
    What table is that? Surely not [Start New Plate].

    The syntax would be
    Code:
    DLookUp("[Process Flow:]","[Start New Plate]","[Plate Number:]=" & [Plate No:])
    This assumes that [Plate No:] is a field in the recordsource for the form or a control on the form, like a combo box. It also assumes that [Plate No:] is numeric. It also assumes a couple of other things, which may or may not apply in this case.
    Bye the way, including special characters in data names (like colon() is not a good idea, as well as having spaces in data names is a bad idea. It can lead to complications that can be difficult to diagnose, so avoidance is best practice.

    You may have some design problems that can only be diagnosed by seeing a copy of your DB.


  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Google: Access domain aggregate function. http://blueclaw-db.com/domain_aggregate_function/

    Or buy a reference book.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    NewProgramPerson is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    8
    Quote Originally Posted by davegri View Post



    What table is that? Surely not [Start New Plate].

    The syntax would be
    Code:
    DLookUp("[Process Flow:]","[Start New Plate]","[Plate Number:]=" & [Plate No:])
    This assumes that [Plate No:] is a field in the recordsource for the form or a control on the form, like a combo box. It also assumes that [Plate No:] is numeric. It also assumes a couple of other things, which may or may not apply in this case.
    Bye the way, including special characters in data names (like colon() is not a good idea, as well as having spaces in data names is a bad idea. It can lead to complications that can be difficult to diagnose, so avoidance is best practice.

    You may have some design problems that can only be diagnosed by seeing a copy of your DB.

    [Plate No:] is actually a short text field, not numeric. What changes would I need to accommodate for this?
    Thanks

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by NewProgramPerson View Post
    [Plate No:] is actually a short text field, not numeric. What changes would I need to accommodate for this?
    Thanks
    http://www.theaccessweb.com/general/gen0018.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If [Plate No:] is not numeric it needs to include single quotes:

    Code:
    DLookUp("[Process Flow:]","[Start New Plate]","[Plate Number:]='" & [Plate No:] & "'")
    Last edited by davegri; 11-03-2019 at 05:32 PM. Reason: format syntax

  12. #12
    NewProgramPerson is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    8
    Thanks, I'll take a look.

  13. #13
    NewProgramPerson is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2019
    Posts
    8
    Appreciate the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dlookup Issue
    By nick243 in forum Programming
    Replies: 3
    Last Post: 09-06-2017, 11:57 AM
  2. DLookup Issue
    By sdel_nevo in forum Programming
    Replies: 10
    Last Post: 05-18-2013, 08:51 AM
  3. Dlookup issue
    By Gilgamesh in forum Forms
    Replies: 5
    Last Post: 12-22-2012, 10:26 PM
  4. DLOOKUP issue
    By gemadan96 in forum Forms
    Replies: 7
    Last Post: 11-01-2012, 06:21 PM
  5. DLookup issue
    By seth1685 in forum Programming
    Replies: 5
    Last Post: 01-12-2012, 08:55 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums