Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318

    DLookup not getting results

    I was trying to do this in a SQL Q but thought it would be easier to do multiple calculations in my form if I used Calculated Controls instead.



    I have a table Lookup_Tbl_Fiscal_Year with Start_Date, End_Date and FSA_FY.

    In my form I want to take the date entered in Last_Signature_Date and look up the FSA_FY. The Last_Signature_Date would fall somewhere between the Start & End dates.

    Here is what I got but no matter what date I enter I get 0 back.
    =Nz(DLookUp "[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Last_Signature_Date]">="[Start_Date]" And "[Last_Signature_Date]"<="[End_Date]"),0)

    I read that this might slow down the system. Is it better to use SQL?
    Last edited by Huddle; 02-09-2012 at 08:39 AM.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Depends on what you're doing, but in general SQL is faster. Try this:

    =Nz(DLookUp("[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Last_Signature_Date]>= #" & [Start_Date] &"# And [Last_Signature_Date]<= #" & [End_Date] & "#"),0)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    It is giving me a #Name? error.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Actually I may have reversed the fields, since you're sort of doing the opposite of what's normal. Try

    =Nz(DLookUp("[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Start_Date] <= #" & [Last_Signature_Date] & "# And [End_Date] >= #" & [Last_Signature_Date] & "#"),0)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    It worked. Thanks. Just curious, what does the # do?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It tells Access that the value is a date/time. For text you'd use single or double quotes, and nothing around numeric values (all of which are based on the data type of the field in the table).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    That makes sense. Thanks for sharing

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    The formula is working when there is data but I am getting #error when there is no data. I have NZ(....., 0). Should I be using something besides 0 since it is a date field?
    Last edited by Huddle; 02-10-2012 at 11:28 AM. Reason: misspelling

  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
    You mean there's no [Last_Signature_Date]? You'd need the Nz() around each instance of it inside the DLookup(). Alternatively, you could wrap your existing function in an IIf() that tested that field and output your desired value if it was Null.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    Here's what I have but I'm getting an #Name? error. Should I not be using IsNull?

    =Iff(IsNull([Last_Signature_Date])," ",DLookUp("[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Last_Signature_Date]>= #" & [Start_Date] & "# And [Last_Signature_Date]<= #" & [End_Date] & "#"))

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It's IIf, not Iff (short for Immediate If).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    Oh yes...I changed it to IIF but it still gives me the error.

    =IIf(IsNull([Last_Signature_Date])," ",DLookUp("[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Last_Signature_Date]>= #" & [Start_Date] & "# And [Last_Signature_Date]<= #" & [End_Date] & "#"))

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, you've reversed the fields. Try the syntax in post 4.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    318
    It works. Thanks for thinking for me today. I'll try to be more diligent in the future.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-14-2012, 07:24 AM
  2. DlookUp
    By cap.zadi in forum Forms
    Replies: 5
    Last Post: 09-21-2011, 07:16 PM
  3. Help with DLookUp
    By focosi in forum Forms
    Replies: 10
    Last Post: 08-08-2011, 07:53 AM
  4. Dlookup
    By pcandeias0 in forum Programming
    Replies: 3
    Last Post: 07-09-2011, 02:31 PM
  5. Dlookup help
    By jcaptchaos2 in forum Access
    Replies: 17
    Last Post: 04-21-2011, 01:33 PM

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