Possible to insert text into pivot query?

[Harris@Z]

Hi,
I suspect this is not possible, but!
I have a pivot query that works correctly.
This issue is that I would like to somehow insert into a column heading, or even row heading, the data range that I am quering so that when copied and pasted, the user is reminded of the date range the data applies to.

Is this at all possible, short of creating a report?

This is my code:
QryStmt = "TRANSFORM Sum(Tech_Orders_Kit.Full_Kits) AS SumOfFull_Kits " & _
"SELECT Companies.CompanyNick " & _
"FROM Companies INNER JOIN (Tech_Orders_Kit INNER JOIN Tech_Orders_All ON Tech_Orders_Kit.Order_ID = Tech_Orders_All.Order_ID) ON Companies.Company_ID = Tech_Orders_All.Company_ID " & _
"WHERE " & oDateRange & " And " & CompanySelected() & _
"GROUP BY Companies.CompanyNick " & _
"PIVOT Format$([Order_received],'yyyy/mm')"


Thanks!

 

[theDBguy]

What does the date range look like? I was wondering if you could use a union query.

 

[Harris@Z]

Thanks for your reply.
The user selects a date from, and a date to.
The oDateRange = " Tech_Orders_All.Order_received >= '2022-11-05' And Tech_Orders_All.Order_received <= '2023-05-04' "

Therefore:
"WHERE Tech_Orders_All.Order_received >= '2022-11-05' And Tech_Orders_All.Order_received <= '2023-05-04' And " & CompanySelected() & _

 

[CJ_London]

Don’t think you can without knowing the number of columns. You could by specifying the columns

PIVOT Format$([Order_received],'yyyy/mm') IN (‘2023/01’,’2023/02’, etc)

you are creating your sql in code so should not be difficult to calculate the required values.

then you’ll have all the requested months anyway but you can then add a union query if still required

 

[Harris@Z]

Thank you, much appreciated for your help