Conditional formatting

[keiath]

Have a problem not sure how to solve, and the standard conditional formatting doesn't seem to work, that i can see.

I produce accounts and for legal reasons they are on a set schedule in which they have to be completed, otherwise a special notice has to go out.

so these are the fields:

[Date] - this is today's date from the system
[Y/E Date] - Year end date for which accounts being produced
[Account deadline date] this is always set 4 months after the year end
[Notice Date] - This is always set 1 month before the final 6 months (or 1 month after[Account deadline date] date)
[Notice Sent] - This is the date actual notice was sent, where accounts could not be produced in time
[Approved Date] - This is the date the accounts were approved, signed and returned.

So i need two things to happen.

1) [Account deadline date] this needs to go Red if system date is higher than [Account deadline date] and no [Approved Date] or [Notice Sent] has been entered, this would go back to normal color if . [Approved Date] or [Notice Sent] has been entered

2) [Notice Date] needs to go Amber once the [Account deadline date] has been reach and no [Approved Date] or [Notice Date] has NOT been entered and then red once the [Notice Date] has been reach and the [Approved Date] nor [Notice Date] has NOT has been entered. Or then back to normal color if [Approved Date] or the [Notice Sent] has been entered



Hope that all makes sense and someone can help.

Thanks

Keith

[rpeare]

so your conditional formatting would be EXPRESSION IS

and then you'd have something like
(date() > [Account deadline date]) AND (isnull([approved date]) AND isnull([NOTICE SENT]))

you're not limited to dealing with one field when performing a conditional formatting check.

[keiath]

So let me get this right, in the conditional formatting build an expression as you state above, will give it and try and will post results

Thanks

[rpeare]

that was a crack at the statement for your first condition, you'd have to build a secondary condition for your second requirement but yes, it should

[keiath]

Hi Got the code as this (Date()>[Account deadline date]) And (IsNull([Accounts Sent to Residents]) And IsNull([Section 20B Sent]))

sys date is 24/01/2014 abd deadline date is 30/11/2013 so this should now go red but its not, I also tried IsEmpty with no luck any ideas what I am doing wrong here?

[June7]

Review: http://allenbrowne.com/ser-36.html

Maybe:

CDate(Format(Date(), "mm/dd/yyyy"))>[Account deadline date] And IsNull([Accounts Sent to Residents]) And IsNull([Section 20B Sent])

or

Format(Date(), "mm/dd/yyyy")>Format([Account deadline date], "mm/dd/yyyy") And IsNull([Accounts Sent to Residents]) And IsNull([Section 20B Sent])

[keiath]

Tried both the dates are different on each record just so you are aware, and the dates are displayed in the correct english format anyway so not sure the date formatting is actually required.

[June7]

You show system date in non-US (non-Access standard) structure. The display format has nothing to do with the way Access stores date value, assuming the field is a date/time type.

[keiath]

Hi I tried that anyway and deadline date still not turning red when the other two fields have no date entered. At a lost at the moment

[June7]

Lets narrow down the possible cause of failure. Try a condition expression without the date part. Try with only the date part.

[keiath]

Value < Date() works fine and field Turns Red

Here i used the standard formatting Field Value Is - Less Than - Date()

[June7]

Use: Expression Is

[keiath]

(Date()>[Account Deadline])

Works fine

[June7]

Now try the IsNull parts without the date condition.

[keiath]

(Date()>[Account deadline date]) And (IsNull([Accounts Sent to Residents])

I then entered this and it no longer go RED when there is no date in the Residents Field (When it should)