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

Stevelin

New Member
Joined
Dec 5, 2010
Messages
8
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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
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:
Code:
(select [Accounting Month] from [T-Accounting-Month] where [ID] = 1)
Or (in context of a whole query):
Code:
SELECT * 
FROM
    [MyTable]
WHERE
    SomeDateField = (select [Accounting Month] from [T-Accounting-Month] where [ID] = 1)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,055
Messages
5,628,346
Members
416,311
Latest member
S991102

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top