Last record backcolor

[Wahab]

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.

 

[John Spencer]

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]

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