Trouble feeding stored date parameters to Access Queries linked to Excel pivot tables

[Stevelin]

I am doing monthly financial reporting using Msft Access 2019 and Excel 2019, and need help in feeding stored date parameters to 30+ Access Queries. It is tedious to update the date ranges within each Access Query manually each month, and the amount of time required to close the books each month will vary, so it is not feasible to use the DateAdd function in the Access Queries in order to automatically roll over the required dates each month.

I created a table of stored data parameters, and use the DLookup function in Access to pull in the stored dates to the queries. The access queries run successfully when Access is open, however an error results when refreshing Excel pivot tables linked to the same queries when Access is NOT open.

Details below ...

I created a table of data parameters in Access 2019. The data parameters table is called "T-Accounting-Month", and it contains two fields: [ID] and [Accounting Month]. ID is formatted as a number, and [Accounting Month] is formatted as a Date value using Short Date format. The table has only one record, with the ID = 1, and [Accounting Month] = 11/1/2018

Next, i went into the QBE screen for each Access Query, and in the criteria section under the Date column I entered the following function: DLookUp("[Accounting Month]","T-Accounting-Month","[ID] = 1") When i run the query in Access 2019, it works exactly as expected and correctly uses the date of 11/1/2018. So far so good. Then I close the Access database.

Next, I open up a workbook in Excel 2019, and it contains a pivot table that is linked to the Access database. Both the Excel Workbook and the Access database are in the same directory. I put my cursor within the pivot table, then hit the Refresh button. I get an error when doing so:

"The query did not run, or the database table could not be opened. Check the database server or contact your database administrator. Make sure the database is available and hasn't been moved or reorganized, then try the operation again."

To test this, i go back into Access and hard code a data range in the same QBE screen, then save the query. Again i exit Access, and reopen the Excel pivot. This time, the Excel pivot table refreshes perfectly.

So ... what is going wrong here? Is the link from Excel 2019 and Access 2019 incompatible with the DLookup function? If so, any other alternative way to automatically feed stored date parameters to a large number of Access Queries?

 

[xenou]

A subquery expression should work instead of DLookup (not sure why dlookup wouldn't work but it's not SQL standard and probably is best avoided in queries that are not directly in Access.

Example:

(select [Accounting Month] from [T-Accounting-Month] where [ID] = 1)

Or (in context of a whole query):

SELECT * 
FROM
    [MyTable]
WHERE
    SomeDateField = (select [Accounting Month] from [T-Accounting-Month] where [ID] = 1)