Crosstab Query - display more than 1 year

[Tony2021]

hello, I have a crosstab query I made with the wizard.  It looks good but I want a year to be appended to the month.  I tried to adjust the format from the current DateAmendSentToBank],"mmm"  to DateAmendSentToBank],"mmm, yyyy" but it didnt seem to work. I only see months. how can i add the year to the month?  Please see pic below. 

TRANSFORM Sum(qryLCPendingIssue.AmountOfChange) AS SumOfAmountOfChange
SELECT tblProjectNames.ProjName, qryLCPendingIssue.letterofcreditID, qryLCPendingIssue.tblLCAmendHistory.AmendType, qryLCPendingIssue.LCName
FROM qryLCPendingIssue INNER JOIN tblProjectNames ON qryLCPendingIssue.ProjectName = tblProjectNames.IDProjName
GROUP BY tblProjectNames.ProjName, qryLCPendingIssue.letterofcreditID, qryLCPendingIssue.tblLCAmendHistory.AmendType, qryLCPendingIssue.LCName
PIVOT Format([DateAmendSentToBank],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

[#2]

@Tony2021 

i think you need VBA to accomplish what you want and a form to show your pivot.

see Query1 (crosstab, with some months missing).

open form1 and see the code on Open event.

[#3]

HI Arnel, thank you very much. I do have a question though. Does the output include more than 1 year? I added a few records in the table in 2022 and 2023 and I refreshed the form but I only see records for one of the years. if I had to choose a limit I would say 2 years if that helps. thank you. Looking forward to your response.

[#4]

@Tony2021 

see the code on the Load event of the form.

the crosstab will display 2 years (starting year is this year).

you can adjust it as to How many years you like, but remember that

the query can only output maximum of 255 columns.

[#5]

@arnel_gp 

Hi Arnel, I am copying over to my db and have a follow up. When you have a sec, how could I also add as value the [AmountOfChange] so basically the display is with dates along the header but then within the data it shows the value of the [AmountOfChange]. I dont think I made that clear in my initial question.  Simply put I need to show the value of [AmountOfChange] instead of the count.  I hope that makes sense.  Let me know if any other questions. thank you very much.  

[#6 · Accepted solution]

@Tony2021 

on Query1, you add the field you want to Sum().

[#7]

I see. I didnt notice you attached a sample. I was checking from my phone. I actually did it myself. It was a little tricky since I could not modify the queryz. I was adding other ID's and such and once I ran the form it removed what I added. I did same as I did in query1 as I did in queryz and that worked. i am good now. Looks really nice. thanks again for the help. Have a good night!