Conditional Format by Week (1 Viewer)

Jax4 · Sep 30, 2019

I've been working with Access for about 5 years and have decided that I need to learn VBA to bring my database up a notch. I have never done any programming before, so this is all new to me.

I have a database that groups records by when a project is due. I would like to change the background color by Week. The problem I'm having is that the weeks are not necessarily consecutive. How to I make the report alternate between colors if there is a week in-between when they are due?

Thank you for any assistance you can give.
Jax

arnelgp · Sep 30, 2019

in design view of your report, use conditional format on the ribbon.
select the week field and add conditional format:

field value is equal 1 (different back color)
field value is equal 2 (different back color)
…
…
note that there is limit to number of condition you may use.

Jax4 · Sep 30, 2019

I tried this, and it didn't seem to work. I created a week field in my query
week: DatePart("ww",[dateField])

Then followed your instructions.

I need to change the background color for all of the fields but base it on the [dateField]

field value is equal 1 (White)
field value is equal 2 (Gray)

Thank you.

June7 · Sep 30, 2019

Only textboxes and comboboxes have Conditional Formatting.

CF rule can reference other controls/fields. Use Expression is: [week]=1

CJ_London · Sep 30, 2019

put this function in a general module

Code:

Function weekcol(Optional w As Integer = -1) As Boolean
Static p As Integer
Static b As Boolean

If w = -1 Then initialise static variables
    p = 0
    weekcol = True
    b = False
    Exit Function
End If

If b = False And w <> p Then
    p = w
    b = True

ElseIf b = True And w <> p Then
    p = w
    b = False

End If
weekcol = b

End Function

In your query have something like

Code:

SELECT *, Weekcol(weeknum) as ColorOn
FROM myTable
WHERE weekcol()=True
Order By Weeknum

note the criteria - that is required to initialise the function

it will produce something like this (-1=true, 0 =false)
ID weekno ColorOn
4 1 -1
1 1 -1
5 2 0
2 2 0
6 3 -1
3 3 -1
8 5 0
7 5 0

then set your conditional formatting based on the colorOn value

e.g.

expression is.....ColorOn=true
set format as required

you only need the one conditional format - so set the normal background to the other color

CJ_London · Oct 1, 2019

an even simpler function

Code:

Function weekcol(Optional w As Integer = 0) As Boolean
Static p As Integer
Static b As Boolean

If w = 0 Then
    p = 0
    weekcol = True
    b = False
    Exit Function
End If

If w <> p Then b = Not b
p = w
weekcol = b

End Function

CJ_London · Oct 1, 2019

I've just added a thread for a similar requirement for forms. What I have already provided above is fine for reports, but not for forms.

This
https://www.access-programmers.co.uk/forums/showthread.php?t=307138

addresses the issue for forms

Jax4 · Oct 1, 2019

CJ_London, thank you for your assistance. I will try it what you wrote. Before I do, I am going to comb through it to understand what it's doing. I am not a programmer but want to learn.

I appreciate the help.

CJ_London · Oct 1, 2019

OK - you will need to understand the difference between Dim and Static (google to find out more) plus for the one on the link, what collections are all about - whilst you are at it also find out about dictionaries.

collections and dictionaries are not something you use every day, but worth knowing they are there

Jax4 · Oct 1, 2019

CJ_London, Thank you so much. This was so helpful and works perfectly. I appreciate your assistance and knowledge.

Jax

Conditional Format by Week (1 Viewer)

Jax4

New member

arnelgp

..forever waiting... waiting for jellybean!

Jax4

New member

June7

AWF VIP

CJ_London

Super Moderator

CJ_London

Super Moderator

CJ_London

Super Moderator

Jax4

New member

CJ_London

Super Moderator

Jax4

New member

Similar threads

Users who are viewing this thread