DLookUp With Dates On A Report

Date literals in Access must be in US format or an otherwise internationally unambiguous format such as the ISO standard for date notation of YYYY-MM-DD.  So, the expression should be:

=DLookup("[Date_Created]","Master Data","[Date_Created] = #" & Format([Datetxt],"yyyy-mm-dd") & "#")

However, you should be able to avoid the DLookup function calls by joining the Master Data table to the report's current RecordSource table or query, and binding the text box controls to the columns from the Master Data table.  The following is an example of the RecordSource for an Invoices form:

SELECT Invoices.InvoiceNumber, Invoices.InvoiceDate, Invoices.CustomerID, Invoices.Customer,

Invoices.Customer AS InvoiceCustomerName,

Invoices.AddressLine1, Invoices.AddressLine2, Invoices.CityID, Invoices.PostCode,

Invoices.Email, Cities_1.CityID AS CurrentCityID, Customers.AddressLine1 AS CurrentAddressLine1,

Customers.AddressLine2 AS CurrentAddressLine2, Customers.PostCode AS CurrentPostCode,

Customers.Email AS CurrentEmail

FROM (Customers INNER JOIN Cities AS Cities_1

ON Customers.CityID = Cities_1.CityID) INNER JOIN (Cities RIGHT JOIN Invoices

ON Cities.CityID = Invoices.CityID)

ON Customers.CustomerID = Invoices.CustomerID

ORDER BY Invoices.InvoiceDate;

In addition to the Invoices table this returns data from two instances of the Cities table and  the Customers table to show values in a complex Invoice form.

You'll find the form in InvoicePDF.zip in my Dropbox public databases folder at:

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

In my same Executive Summary Report, I want to use the DLookup function to call for the following information onto the report:
~ Date and the format is a Medium Date
~ CompanyName
~ Address 1
~ Address 2
~ URL
~ Email
~ Tel
Thank you

You are approaching this from the wrong angle. Dlookups should only be used as a last resort. The best way of populating a report is to create a query that returns all the data you want in your report. Then use that query as the Recordsource of the report. Designing an unbound report (as it appears you are doing) is not good practice.

I'm assuming this Executive Summary is the same one in this thread; DLookup To Calculate Fields On A Report - Microsoft Community which called for a report that summarized incidents by status.

Where is the information that you describe coming from? Is it the company creating the incidents or is it your company info? The fields you mention are not in the Master data table you showed in your other thread. You really need to provide more info as George said, for us to help.

It would appear that your code will only work with dates which do not have a time component.

All dates are stored in Access as date with time: 6/23/2025 7:11:31 AM

That means you have to account for the time portion in your dates in this table.

Moreover, Format() only changes the display, not the actual value. And it converts the result to a string. On both counts, the code shown is not up to the task of selecting one record based on the date in the field Date_Created.

But even if we sort that out, this does not look like a very useful expression.

It retrieves the value of a date field which is supposed to be equal to a value already displayed in a different control. Why not just display the value from that second control, Datetxt?

So, before we dive into details on the expression, maybe it would be worthwhile to understand what the goal here is and whether this expression is useful as is.

Please, below is the code for the Date:
=DLookUp("[Date_Created]","Master Data","[Date_Created] = #" & Format([Datetxt],"dd-mmm-yyyy") & "#")

but it is not working. Still figuring it out ...

However, other fields such as: Document Version and Classification had worked ...

Thank you

So, what is the problem? What have you tried? Can you show us your code?