As many of you know, you can't build range names (whether they are one cell, or a range of cells) in Access. You can, however, construct global variables. One of the global variables I made to account for the number of billable hours in one year is as follows:

Option Compare Database
Option Explicit

Public Const HoursYear As Integer = 1824

Function GetAnnHrs()

    GetAnnHrs = HoursYear
    
End Function

I can then use this in a query by multiplying one of the fields of a table by the function GetAnnHrs(), and the result in the expression shows that if there is $100 per hour in the rate field, the expression will calculate $100 times 1,824, or $182,400. This works perfectly well in Access, however, if I want to go into Excel and create a pivot table with an Access query being the source, Excel doesn't "see" the query. I have to go into the database and replace the function GetAnnHrs() with 1824. When I do this, Excel will then "see" the query when I want to build a pivot table in Excel.

What do I have to do in Excel so that I can continue to use the global variable in Access? Also, are there any other approaches in Access that are simpler that constructing global variables?

Thank you,
Bruce

---

Reply from Richard Rost:

I'm not exactly sure what you're trying to do, but instead of using a query and then basing your Excel PivotTable on that query, why not use a MAKE-TABLE QUERY to construct a TABLE in Access, and then use THAT in Excel?