Format Text Box, Based on Variable

[vipa2000]

Within a report i have a text box referenced 'Week'. The data from this comes from a query that references a cell called 'Week'. 'Week' gets it data from an adjacent cell called 'Lat#finish date'. It uses the code Week: Format([lat#finish date],"ww") to return the week number.

I want for the report cell called week to turn red if the week is todays 'week' or less, yellow for a week in advance and green if three weeks hence.

Sorry if a bit convoluted but i wanted to make sure you had all the facts. I am trying to use a traffic light scheme for planning work.

Bonus question using format, is Monday the start of the week? I need it to be.

regards

vipa

 

[vipa2000]

thanks chaps, found a thread which had the right wording to allow me to further my search. Using Format(Now(),"ww") and conditional formatting

regards

vipa

 

[ThreeCrow]

Are you using Access 2000 or higher

You can use "Conditional Formatting"

 

[vipa2000]

Access 2000 ThreeCow. Found a slight problem. I have used conditional formatting based on the week no and thought whow, but then realised once i get to year end we go back to 1. My format of Format(Now(),"ww")+1 goes to pot. Any ideas? Am i better creating another field with year or am i making this too complex. Also I have run out of conditional formatting types.

regards

vipa