Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44

    Unanswered: Help with Dlookup() condition

    Hi all;

    I am working on a new project for someone and I want to create 2 fields by function on an after update on the form.

    My problem is in the DLookup line of code: which is this:

    Me.Officer_Name = DLookup("[Officer_Name]", "Officer_Qry", [Officer_Number] = Me.Officer_Number And [Current] = True)

    The names are correct and the query is ok but I need the 2 criterion to select the correct name (in case of officer number changes, new hires, Transfers and so on [the number may be reused for someone else down the line] but only one number and current will be checked off) can someone fix it so I wont get a TYPE MISMATCH error 13.

    Officer number is a number field and Current (in the query) is a boolean.

    any help appreciated.

    TIA
    Kevin

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    DLookup("[Officer_Name]", "Officer_Qry", "(([Officer_Number] = " & Me.Officer_Number & ") And ([Current] = True))")

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44
    Thanks izyrider, that did the trick. I had tried several ways before posting nd did not even think of aadding (). it works now.

    Kevin

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    glad it worked out, but take care -- the (()) are mostly cosmetic (though in messy expressions they prevent A going down the wrong road).

    the real problem was that the criteria also have to be in quotes so you have to concatenate in your form field....

    DLOOKUP("blah", "tblBlah", "Field = " & stuffFromYourForm & "blahblah")

    izy
    currently using SS 2008R2

Posting Permissions

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