Conditional Formatting - Only One Row

[kujeremy]

I have a report set up and I can successfully format all returned rows for a given column as I want them. However, what I want to do is only format the latest row on the report.

The table that the record comes from does include an End_Date field which is also populated on the row, and the data comes from entries which are updated weekly so the end dates fall like: 09/11/2016, 09/04/2016, etc.

What I am trying to do is when the weekly report is generated, for this weeks data which ended on 09/11/2016 only apply the formatting to that specific row - so even if the previous week which ended 09/04/2016 had been formatted when it was the current record, it no longer would be.

Hope this makes sense and as always thanks for any insight.

The info I am working with is:

End_Date - field for the last day of the reporting week
Ancillary_Thank_You - field which is the column data to be formatted
rpt_WeeklySales - name of the report this information is on
tbl_WeekySales - name of the table that contains the two fields

 

[Minty]

You simply need to determine the current end date (As in either the next or last Sunday I think) at any given time you run the report.

To get the last day you can use a function;

Public Function LastWeekday(dDate As Date, iWeekday As Integer) As Date

    iWeekday = Weekday(Now(), iWeekday)

    LastWeekday = Format(Now - (iWeekday - 1), "mm/dd/yyyy")

End Function

Then in your query or conditional formatting use LastWeekday(09/21/2016, vbSunday) to get the current last end date

 

[kujeremy]

Minty - thanks for the reply, I do not believe that I am following correctly or at minimum doing something incorrect.

What I did was:

Went to the Visual Basic screen -> made a new module and copied the code you supplied and saved. So now I have a "Module2" with your code.

Then - went to conditional formatting of the field on my report and added an Expression Is: LastWeekday(09/21/2016, vbSunday)
(I'm not sure if this was meant to be entered in exactly like that or not)

When I try to run the report it gives an entry box for "vbSunday". I've tried several dates in it and it does not appear to have any impact on the results.

Sorry if I'm being dense and not doing this appropriately.

This report - is typically ran on Tuesday (Although could be any day Tues - Friday) for the prior week. So next Tuesday (09/27/2016) the reporting week will be for 09/18 - 09/24.

What I am doing is, this report shows a value for a specific web pages views for a week. It also includes the average visits to that page (This average is based on the current years reporting - the total sum divided by the number of weeks being reported).

I set up the conditional formatting that if the weeks value is less than the average value, it shows in red. If the weeks value is 25% or more above the average it is in green. This works fine for all weeks on the report but my concern was - because the average is calculated each report run there is potential for one week a result to be green, and then depending on subsequent results (raising or lowering the average) that same week could turn red for being below the threshold at a later date - I was thinking this may confuse some of the audience of this report and it may be better to base the conditional formatting only for the current weeks results.

Don't know if all of that helps or not but it may be that I'm over thinking this or there's a better way to accomplish this.

 

[Minty]

You may need to enter the weekday as a number. Personally i would put the function in the query as an extra column called Lastweek : LastWeekday(Date(), 1) . Then in your conditional format Expression is End_Date = LastWeek and change the colour

 

[kujeremy]

Thanks again - I've either implemented wrong or something.

I added a new column to the query that brings back this report information:

Field -> Expr1:[LastWeek]
Criteria -> LastWeekday(Date(),1)

I then added conditional formatting to the field on the report of:

Expression Is: [End_Date] = [LastWeek] and set it if true to turn text Red

When I open the report I get a dialog for "LastWeek" - Currently if I enter in 09/18/2016 it brings back all results (Last end date on the report was 09/17/2016) but there is no formatting applied to any row.

If I enter any other date the report is blank.

I appreciate your help with this - but I think I'm going to stop pursuing this and leave the report as is or just implement the formatting across all records.

Thanks!

 

[Minty]

Field -> Expr1:[LastWeek]
Criteria -> LastWeekday(Date(),1)

You are Close !
Field > LastWeek: LastWeekday(Date(),1)
No Criteria

I then added conditional formatting to the field on the report of:

Expression Is: [End_Date] = [LastWeek] and set it if true to turn text Red

This bit is correct with the changes made above.

 

[npatel]

Thank You...