I have created the attached report from a select query, for inventory levels of items that have stock, joined to a crosstab query, that shows the sales level for each of the last 12 months. The last 12 months will change every month. For the report, in the crosstab query, I used fixed column headings, e.g., Per1, Per2 , etc.. I used formulae in the report headings to show the actual period name, e.g. 201909, 201910, etc.
My problem is that the users now want this data sent to Excel with the actual period name rather than the fixed column heading. Tried OutputTo and Transferspreadsheet but they both carry over the fixed column head e.g. Per1, Per2, etc.. Have done a lot of googling to see if I could score a snippet, but to no avail.
Any assistance would be appreciated.
The SQL for the query I used for the report is below:
SELECT [777tblforreport].Rep, [777tblforreport].Cust, [777tblforreport].CustItem, [777qtyOrd2].OdrQty, [777tblforreport].[Spec#], [777tblforreport].Compnt, [777tblforreport].Stocked, [777tblforreport].skid, [777tblforreport].OnHand, [777tblforreport].CustPO, [777tblforreport].LstInv, [777tblforreport].Dt_of_Per1, [777tblforreport].Per1, [777tblforreport].Per2, [777tblforreport].Per3, [777tblforreport].Per4, [777tblforreport].Per5, [777tblforreport].Per6, [777tblforreport].Per7, [777tblforreport].Per8, [777tblforreport].Per9, [777tblforreport].Per10, [777tblforreport].Per11, [777tblforreport].Per12, [777tblforreport].Dt_of_Per12, [777tblforreport].DaysInInvty, [777tblforreport].ageddaysused AS AgingDaysUse, [777tblforreport].TodaysDate
FROM 777tblforreport LEFT JOIN 777qtyOrd2 ON ([777tblforreport].[Spec#] = [777qtyOrd2].INVBITEMNO) AND ([777tblforreport].CustNo = [777qtyOrd2].CustNo)
WHERE ((([777tblforreport].Rep) Like "*" & Forms![777FrmMainMenu]!Combo12 & "*") And (([777tblforreport].Cust) Like "*" & Forms![777FrmMainMenu]!Combo43 & "*"))
ORDER BY [777tblforreport].Rep, [777tblforreport].Cust, [777tblforreport].CustItem;