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

Harris@Z

Registered User.
Local time
Today, 20:09
Joined
Oct 28, 2019
Messages
97
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!
 
What does the date range look like? I was wondering if you could use a union query.
 
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() & _
 
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
 
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom