CODEWITHSUNDEEP
ms-accessms-access-query
walterpayton34
walterpayton34

Reputation: 35

How can I display the Query name as a column when I actually run the query?

I run a bunch of queries that check for inconsistencies in our data set. I literally have hundreds of these queries and when one of them has a result I forward it as an excel file to the appropriate colleague. I'd like to include the actual name of the query as a field in the grid view that I used to create the query. For example, if the query is named "Test 1961", I'd like to create a column inside the query labeled "Query Name" that would then, actually display "Test 1961" in a column labeled "Query_Name" in the results table. I searched google and it said the entering """ would generate the query name. I tried that suggestion and just get an error message.

I entered """ and also just " and also '"'but it did not display the name of the query being run.

Upvotes: -1

Views: 79

Answers (1)

Gustav
Gustav

Reputation: 55981

Create a function to retrieve the name of the (first) opened query:

Public Function CurrentQueryName() As String

    Dim Query       As DAO.QueryDef
    Dim QueryName   As String
    
    For Each Query In CurrentDb.QueryDefs
        If SysCmd(acSysCmdGetObjectState, acQuery, Query.Name) > 0 Then
            QueryName = Query.Name
            Exit For
        End If
    Next
    
    CurrentQueryName = QueryName
    
End Function

Then include that function in your relevant queries:

SELECT 
    CurrentQueryName() AS SourceQuery, 
    <other fields>
FROM
    <tables>

Now you can rename or copy your queries at will and still have the actual query name returned when a query is opened.

Of course, it will only run as expected if no other queries are open.

Upvotes: 1

Related Questions