asked on

how to conditionally format (bold) the most current week's records in a subreport

Humming right along here. Placed 2 subreports in 1 main report - YAY! Got rid of a couple of dreaded unexpected parameters.

Now I would like to BOLD records written in the most current week of quotes written in last 60 days (one of the subreports).
If I use between [QDate]+(1-Weekday([QDate],7)) and [QDate]+(7-Weekday([QDate],7)), WeekStartDate and WeekEndingDate respectively. HOW do I tell access to choose only the 'newest week' to bold?

Or if there's a better way ...

Hope my question makes sense?! Please let me know how to make it clearer.

Richard Daneke

If you are using the last 60 days, you can simplify the selection on QDate to
between Date()-60 and Date() or
> Date()-60 (assuming you have no future dates) to return QDates within the past 60 days.

M M

ASKER

Hi Richard. I can see I wasn't clear enough. My qry pulls quotes written in the last 60 days (that's where I've already used between Date()-60 and Date(). I have a subrpt based on that qry for a weekly report - ending on Fridays, ergo this: [QDate]+(7-Weekday([QDate],7)). The weekly report lists written sales for the week per Rep.

I want to bold the quotes written in the current week of the subreport of written quotes in the last 60 days. How do I/what do I need to do to pull up the quotes written 2/11/207 thru 2/17/2017 for this past week's report? And then 2/18/2017 thru 2/24/2017 for next week's report, and so on?

Hope this clearer? And do-able? I just can't seem to figure out how to accomplish it. Thanks.

ASKER CERTIFIED SOLUTION

Richard Daneke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

M M

ASKER

RICHARD, THANK YOU!!! This is the quickest I've gotten an answer to-date! Mostly it's my fault, bc I don't know how to ask what I don't know!!! So thank you.

Could you indulge me by explaining the pieces and parts of this expression? And why/how it works?
So [QDate]+(7-Weekday([QDate],7)) isn't even relevant, y/n?

M M

ASKER

ooh ... found this:
The syntax for the DatePart function in MS Access is:
DatePart ( interval, date, [firstdayofweek], [firstweekofyear])
interval is ww (or week); date is QDate; what is [firstdayofweek] & [firstdayofyear]?

What does the -1 do in your solution?

OK, this is going to be really dumb (at the risk of muddying this up):
why wouldn't just DatePart("ww", Now())-1 work? Does the left part (before the = sign) always have to proceed a DatePart function?

Richard Daneke

The function DatePart permits us to find the week number of any date. We use the date in the report record and compare it to today using Now()

Does the left part (before the = sign) always have to proceed a DatePart function? Yes.
Conditional formatting for field value is would only work on the date field.

To format the full line, you need to create the expression that uses the week number from the date field in the current report record against the week number of today.

The phrase DatePart("ww", Now())-1 equates to the week number for today minus 1(hence, last week)
so the bold is applied when the date in the report record is in the previous week.

what is [firstdayofweek] & [firstdayofyear]?
arguments in square brackets [ ] are optional. so firstdayofweek and firstweekofyear have been omitted
firstdayofweek by default is Sunday - it fits. firstweekofyear permits you to set the number for the first week in a year, but does not change the results here

So [QDate]+(7-Weekday([QDate],7)) isn't even relevant, y/n? no. not relevant.

M M

ASKER

Many thank you's to Richard!!!

Richard Daneke

Thank you. I enjoyed helping.