Thread: Color-coding Access Report
03-12-13, 15:53 #1Registered User
- Join Date
- Dec 2012
Unanswered: Color-coding Access Report
I have to color-code months based on when billed & when paid.
Problem: when I include the criteria date fields to the SQL Select statement below, the data gets stacked- one each row rather than grouping like records together on one single row
(for example: stacked - one company will show Nov on one row, Dec on another. grouped - shows all data for the company on one row).
is there some way to do a iif(month(date1) <> month(date2),T,F) inside of the sql? I would like to compare the 2 dates without SELECTing them. I can manipulate further using VBA. TIA
TRANSFORM Sum(AST.[Splits Amount]) AS [SumOfSplits Amount] SELECT AM.AgentID, AST.GroupID, AST.CarrierID, AST.Loc, Sum(AST.[Splits Amount]) AS [Total Amount] FROM AgentMaster AS AM INNER JOIN [Agents Splits Transactions] AS AST ON AM.AgentID = AST.AgentID WHERE AST.[splits paid date] >= Forms!frmReports!StartDate GROUP BY AM.AgentID, AST.GroupID, AST.CarrierID, AST.Loc PIVOT Format([Earned Premium Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");