Possible to insert text into pivot query? (1 Viewer)

[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

 

[Pat Hartman]

Open the query in QBE view and add a new column. have it pull the date range from your form. It will appear on every row because that's the way queries work. Make sure the Crosstab: line is set to "Row Heading" for the new column.

 

[Harris@Z]

Thank you, much appreciated for your help

 

[Pat Hartman]

You're welcome The wizard that most people use to build the crosstab only allows three "Row Heading" fields to be selected and doesn't allow you to create fields such as one to get a value from a form. However, once the query is saved, you can open it up and modify it to add as many columns as you want for "Row Headings" keeping in mind the total column limitation for all queries is 255.