Formula for conditional formatting

[El Jagang]

I have a two-part question.

1) The database I'm building has a [Medical Test] field. This test needs to be taken every 5 years by people under the age of 40, and every 2 years by people above the age of 40. The people's ages are entered in a field called [DOB].

I'd like the conditional formatting to have the field background go red when the test is due. I've tried an IIF formula, and while it can determine whether a member is over or under 40 by writing IIF((Date()-[DOB])/365<40), it then needs to calculate 2 years or 5 years (depending on the age of the person in question) from the date entered in [Medical Test] in order to affect formatting. I don't know how to enter that second equation in IIF (or if it's even possible to do so).

I asked someone about programming this in VB, however I was told that it's "too hard" because I've never taken a course to learn that program. I don't mind teaching myself, but I don't know where to begin in order to learn this on my own.

2) How do I change a field's fill colour using the expression builder? I've run a few Google searches (using different wording), and I haven't been able to find the appropriate command.

Any help would be appreciated!

 

[AccessBlaster]

This tutorial is about reports, however the principles should be similar.

https://www.youtube.com/watch?v=G911ll1iYhM

 

[pbaldy]

That's only part of a valid IIf() function, which has 3 parts. It might look like:

IIF((Date()-[DOB])/365<40, 5, 2)

so you add that to a DateAdd() to get the due date:

DateAdd("yyyy", IIF((Date()-[DOB])/365<40, 5, 2),[Medical Test])

so in Conditional Formatting you can test that against the current date:

Date() > DateAdd("yyyy", IIF((Date()-[DOB])/365<40, 5, 2),[Medical Test])

 

[pbaldy]

Oh, and what if my last test was at 39? Is my next at 44 or 41? With your formula, it will be 41.

 

[El Jagang]

Oh, and what if my last test was at 39? Is my next at 44 or 41? With your formula, it will be 41.

I just checked with my employers, and the answer made my problem even trickier.

If a test is taken past the age of 35, the 5-year rule still applies, but the next medical test has to be taken no later than the age of 42. So this means that a 36- or 37-year-old would have the five year interval, but a 39-year-old would have to do his medical test again 3 years later.

I'm starting to believe that I won't be able to get away with an IIF expression.

 

[El Jagang]

This tutorial is about reports, however the principles should be similar.

I'll watch this from home tonight; no video streams of any kind are allowed where I'm doing my internship.

 

[pbaldy]

It is starting to sound like a custom VBA function will be simpler. Regarding

I asked someone about programming this in VB, however I was told that it's "too hard" because I've never taken a course to learn that program.

A large number of us here have never taken a course in it, me included. Dive in, the water's fine!

 

[El Jagang]

A large number of us here have never taken a course in it, me included. Dive in, the water's fine!

I definitely plan to, however my time here as an intern is short, and my performance is being graded. I don't even know where to start; I've basically been told that I'm trying to run before I learn to walk (when I talked about building an IF formula). A lot of the examples I'm seeing online include things I don't understand, and the way they are worded makes it seem like I should understand those other things before I try that IF function.

I'll start watching some YouTube videos on the subject over the next few evenings and see where that gets me. Thankfully, my supervisor understands the limitations of my education (and the time constraint).

 

[El Jagang]

Oh and thanks for Date() > DateAdd("yyyy", IIF((Date()-[DOB])/365<40, 5, 2),[Medical Test]).

It worked like a charm, and that's a good start for where I'm going.

 

[pbaldy]

No problem, glad it worked for you.