Results 1 to 9 of 9
  1. #1
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128

    Question Use DLookup to Retrieve Field Value from a Date?

    I have an input form that a user can insert a Start Date and End Date, and then open a report with charts in those date ranges.



    I need to populate some of the field values in the report with field values that correspond to the End Date only.

    Can I use the DLookup expression for this? I've been trying this, along with other expressions, and can't get the result I need.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    It's funny as I've actually been looking at this site trying to figure this out.

    I have used the following but continue to get an error:

    DLookup("Field Name", "Table Name", "[End Date]"

    The [End Date] is the name of the End Date from the form input. I have tried without the quotes and without brackets, but I still get an error for that field value.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is the name of the Form involved?
    As a suggestion, you should adopt a naming convention that does NOT have spaces or special characters in names.

    This is the format/syntax you need:
    You could also use a form control in the DLookup function. For example:

    DLookup("CustomerID", "Orders", "OrderID = " & Forms![Orders]!OrderID)
    This example would return the CustomerID field from the Orders table for the record that is currently being displayed in the Orders form (based on OrderID).

  5. #5
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Still not getting it right.

    I have it like this now:

    = DLookup("Well Name", "EOSSummary Table, "End Date = " & Forms![EOS Summary Table]!Days)

    Well Name is the field. EOSSummary Table is the table. Days is the field for dates of each record. And End Date is the date coming from the input form.

    I am told that this is invalid syntax by Access.

  6. #6
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Alright, I found another site that states it should be according to date, so now I have:

    =DLookUp("Well Name","EOSSummary Table","Well Name = #" & [Forms]![EOSR Dates]![End Date] & "#")

    Which still doesn't work.

  7. #7
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    No worries. Ended up putting in:

    [Forms]![EOSR Dates]![End Date]

    into the Criteria of the query for the report, and seems to be working now (on my tester DB). Could've sworn I tried this before, but may have been putting in the wrong table...

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Something doesn't sound right.
    This
    =DLookUp("Well Name","EOSSummary Table","Well Name = #" & [Forms]![EOSR Dates]![End Date] & "#")
    is trying to match Well Name with a Date????

    What are you looking up is the first parameter, the table involved is second, and the condition is third.

    But Well Name = ..Date.. is not sounding right

  9. #9
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Doesn't matter now, as the query resolution works. Thanks for trying.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-09-2012, 07:43 PM
  2. Replies: 1
    Last Post: 05-17-2011, 05:19 AM
  3. How to retrieve field names in a table
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 12-05-2010, 09:09 PM
  4. Replies: 3
    Last Post: 10-06-2009, 02:11 PM
  5. Replies: 0
    Last Post: 06-03-2009, 10:25 PM

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