Conditional Formatting on Form (1 Viewer)

[NixonShaun]

Hi All,
I am not a developer but someone who uses Access to streamline work tasks.
current situation: if an application for services is turned in, i have 60 days to determine eligibility for our services, if deemed eligible i have another 90 days to draft a plan of support.
I have all kinds of demographic info on the form and then some specific fields, including: ApplicationDate (short date formatted), EligibilityDate(short date formatted), PlanDueDate (short date formatted), EligibilityDetermination (check box), PlanDeveloped (check box).

What i would like to do is have the color of the EligibilityDate field change color to red if eligibility is overdue, i.e. past 60 days from ApplicationDate.
but only if Eligibility is not established, the EligibilityDetermination check box is not checked.

Also, if the EligibilityDetermination check box is not checked, and we have 2o days remaining i would like the color to be blue.

finally, if EligibilityDetermination is true, checkbox is checked then the text should remain black as it is not a concern at present.

this is what i have so far.

In the EligibilityDate field using conditional formatting i have three rules:

1) [EligibilityDetermination]=True (text black)
2) [EligibilityDetermination]=False And "Value">[ApplicationDate]+60 (text red and bold)
3) [EligibilityDetermination]=False And "Value">[ApplicationDate]+40 (text blue)

My problem is that if the EligibilityDetermination check box is checked the field text is black but if unchecked red all the time, never blue. it seems the red and blue rule somehow cancel each other out as they are essentially the same, i am just not sure how to get around it.

any ideas would be appreciated.
thanks everyone.
shaun

 

[pbaldy]

I'd try using the field name, though "Value" might be okay.

[EligibilityDetermination]=False And [EligibilityDate]>[ApplicationDate]+60

 

[isladogs]

Agree with Paul regarding use of field name.
Only use Value = for simple expressions involving the field being formatted

As all your CFs involve EligibilityDetermination, you must use 'Expression Is'

Where you find one CF overwrites another, you need to consider the logic & if necessary change the order. The conditions are tried in turn starting with 1. So I think you should swop 2 and 3

1) Expression Is [EligibilityDetermination]=True (text black)
2) Expression Is [EligibilityDetermination]=False And [EligibilityDate]>[ApplicationDate]+40 (text blue)
3) Expression Is [EligibilityDetermination]=False And [EligibilityDate]>[ApplicationDate]+60 (text red and bold)

ALSO what happens if
Expression Is [EligibilityDetermination]=False And [EligibilityDate]<=[ApplicationDate]+40

If the answer is text = black, then you don't need condition 1

 

[NixonShaun]

Thanks great answers.
I goofed up somewhat though. taking a look at the form, it is based on a query i designed where EligibilityDate was created in the query design by inserting "EligibilityDate: [ApplicationDate]+60"
so on the corresponding form if i enter in an application date, it automagically fills in the eligibility date 60 days from the application date, thus giving me a 60 day window to complete eligibility.

I am not sure if this messes up the conditional formatting.
based on this, i created a field called Edate: Date ( ) on the query and then added it to the form, so it is a field with today's date in it.
conditional formatting on this field based on your ideas seem to work well.

[EligibilityDetermination]=False And [EligibilityDate]>[Edate] is in green as green is good and then,
[EligibilityDetermination]=False And [EligibilityDate]<[Edate] is in red as this is overdue.

seems to work ok but not sure if this is sufficient or if it meets good programming practices.

 

[pbaldy]

You can use the function in CF:

[EligibilityDetermination]=False And [EligibilityDate]>Date()

 

[isladogs]

Its simple, breaks no rules and works. Go for it.