Display Month of First Week

[Abdulkawi]

Hey,

I'm trying to display the month of the begining of each week in its month in my report (see attachment). So each month would be act as a group (heading) for those 4 weeks in its month.

I'm also trying to show the total amount for each week on the right hand side (see attachment). I can get it to show for all the weeks, but not for each individual week.

All the help will be greatly appreciated, as always!

See attachment for the query, report design and preview.

Thanks.

 

[PNGBill]

Here is an sql used as the record source for a Report.

SELECT QLoanIssuedCollectTotalsQuery.LDPK, QLoanIssuedCollectTotalsQuery.TRNACTDTE, [COLOR=Green]Month([TRNACTDTE]) AS MonthNumber, (Format([TRNACTDTE],"mmmm")) AS MonthName, [/COLOR]QLoanIssuedCollectTotalsQuery.Principal, QLoanIssuedCollectTotalsQuery.Application, QLoanIssuedCollectTotalsQuery.Processing, TBLLOAN.ADPK, [ADFirstname] & " " & [ADSurname] AS Name, [BDNam] & " " & [BDBr] AS Bank, TBLACCDET.ADBKACCNUM, TBLLOAN.LDPayMth, TBLLOAN.LoanAppID
FROM TBLBANK INNER JOIN (TBLACCDET INNER JOIN (TBLLOAN INNER JOIN QLoanIssuedCollectTotalsQuery ON TBLLOAN.LDPK = QLoanIssuedCollectTotalsQuery.LDPK) ON TBLACCDET.ADPK = TBLLOAN.ADPK) ON TBLBANK.BDPK = TBLACCDET.ADBk
WHERE (((QLoanIssuedCollectTotalsQuery.TRNACTDTE) Between [Enter first date of period] And [Enter last date of period]))
ORDER BY QLoanIssuedCollectTotalsQuery.TRNACTDTE;

The Green typing is two new fields.
One has Month Name and the other Month Number.
Use Month Number as the Report Group Header and Footer as you can Sort on this. Make the control Invisible and have Month Name displayed.

If you just use Month name, you will have December before January.

 

[PNGBill]

Here is a screen shot.