If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Last record backcolor
Hi
I have samll report with fields, trnDate and Customer and Balance. Balance is the running sum, I want to change the background color of Balance if the transaction is last for that month. Pls I dont want to create group for month. |
#2
|
|||
|
|||
Last record backcolor
Since you don't want to do it the easy way you will have to modify the query
that is the report's source. Personally I would group by month and have a group header with a control that was set to get the count of records in the group. You can set the visible property of the header to no so it has no effect on how the report looks. Then I would add a control to the detail section and get a running count over the group. When the Running count was equal to the count of records in the group, I would change the background color. If you want to do this with a query. SELECT trnDate, Customer, Balance , (Select Max(trnDate) FROM TheTable as TEMP WHERE Format(Temp.TrnDate,"yymm") = Format(TheTable.TrnDate,"yymm") AND Temp.Customer = TheTable.Customer) as LastDate FROM TheTable Then all you need to do is compare trnDate to LastDate to set the background color. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Wahab wrote: Hi I have samll report with fields, trnDate and Customer and Balance. Balance is the running sum, I want to change the background color of Balance if the transaction is last for that month. Pls I dont want to create group for month. |
#3
|
|||
|
|||
Last record backcolor
thank you very much Mr. John,
I tried lot but I couldnt, shall I send my samll db file so you will get an idea? This will help me a lot, thanks and regards Wahab "John Spencer" wrote: Since you don't want to do it the easy way you will have to modify the query that is the report's source. Personally I would group by month and have a group header with a control that was set to get the count of records in the group. You can set the visible property of the header to no so it has no effect on how the report looks. Then I would add a control to the detail section and get a running count over the group. When the Running count was equal to the count of records in the group, I would change the background color. If you want to do this with a query. SELECT trnDate, Customer, Balance , (Select Max(trnDate) FROM TheTable as TEMP WHERE Format(Temp.TrnDate,"yymm") = Format(TheTable.TrnDate,"yymm") AND Temp.Customer = TheTable.Customer) as LastDate FROM TheTable Then all you need to do is compare trnDate to LastDate to set the background color. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Wahab wrote: Hi I have samll report with fields, trnDate and Customer and Balance. Balance is the running sum, I want to change the background color of Balance if the transaction is last for that month. Pls I dont want to create group for month. . |
Thread Tools | |
Display Modes | |
|
|