asked on

Conditional formatting Datasheet subform

I have set 3 criteria using the conditional formatting, however, that don't seem to be working correctly.

The field is a date field and I am checking to see if the field1 <= DateAdd("d", 45, Date) then change the background to red and font yellow. the field data = 2/01/2008 and current date = 2/17/2009 why doesn't it display the conditional formatting?

Karen

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

Thanks that seems to return the conditionally formatting, however, my calculation is not returning what I was hoping for.

I need to check and see if the date <= 30 days from current date, also note some of the dates can be quite old so I want to check the year and if the year is > than 1 year time span then format a different color, else than check if falls withing the 30 or 45 day criteria.

so what do I need to do to the fomula to check the time frame criteria?

Karen Schaefer

ASKER

nevermind I figured it out by usin

between Date() and DateSerial(Year(Date()),Month(Date()),Day(Date()+45))
or
less than DateSerial(Year(Date()-1),Month(Date()),Day(Date()))

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

How about using DateDiff, for example:

DateDiff("d","1/01/2009",date())<=30
returns False

DateDiff("d","2/01/2009",date())<=30
returns True ...

mx