access field conditional formatting

PeterBaileyUk
PeterBaileyUk used Ask the Experts™
on
I have a date field and a classdate field and a cancelled field.

I am trying to make the date field go red to show cancelled when the cancelled field says "cancelled" and the classdate (cancelled date) is same as date.

The code so far
[Date]=Format(Now(),"Short Date") And [Cancelled_Indicator]="Cancelled" And Format([ClassDate],"Short Date")=Format([(Date],"Short Date")

Open in new window


and for green (to show class ok to go ahead ie not cancelled)

[Date]=Format(Now(),"Short Date") And [Cancelled_Indicator]="Not Cancelled" And (Format([ClassDate],"Short Date")<>Format([(Date],"Short Date"))

Open in new window


I cant get it to work yet the form does start ok
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
With your conditions when Cancelled indicator is Cancelled and Date is not equal to ClassDate field will be green.
Look at sample with some modifications:
Date field ids renamed to Dt, because Date is reserved word in Access, Cancelled indicator was changed to boolean (Yes/No), 3 formats are used:
1. Dt = ClassDate and Cancelled_Indicator = True (RED)
2. Dt <> ClassDate and Cancelled_Indicator = True (Light RED)
3. Other (Green)
DBCond.accdb

Author

Commented:
before today I had just
[Date]=Format(Now(),"Short Date") And [Cancelled_Indicator]="Not Cancelled"

Open in new window

This showed that the DT was green if not cancelled class.
it showed red with
[Date]=Format(Now(),"Short Date") And [Cancelled_Indicator]="Cancelled"

Open in new window


Today it went red with a class that was cancelled mid week as it had no conditioning on date.
So it need be green if today has a class and not cancelled and red if today has a class and cancelled

the additional requirement I had in mind was that if the cancelled date (this is stored in a table) is todays date then it show cancelled
but shows green otherwise ie the cancellation date was not today.

I hope I made sense
If you don't like my sample, you can go with the following green criteria:
[Date]<>Format(Now(),"Short Date")
What default color is used?

Author

Commented:
your sample is great maybe i just need merge them, the brighter standard green
If you can upload your form, I can try to modify it

Author

Commented:
Ok Ive attached do not download if on mobile for data etc.

The top left menu choice has the subform in it where you can see the data of the class and are two non visible fields attached to the tblcancellation.

the conditional formatting is on that date field.

its orange as i was experimenting to see what was triggered but you get the point if the field is green and todays date it means class go ahead if its cancelled it should be red.  If todays date is 26th then a cancelled class of 19th should have no effect.
CourseTracker--ee.accdb
Try this. Cancelled - Yellow, Cancelled today - Red, other - Green
CourseTracker--ee.accdb
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Another option would be to create a function which can be called from within your Conditional formatting expression.  You would pass the function your date fields and the value of your Cancelled_Indicator field.  The function would return a value associated with whatever condition the values you pass it returns.  Then, in your conditional formatting, you would set the expression to something like:

Expression:  fnMyFunction([Date], [ClassDate], [Cancelled_Indicator]) = 1

BTW, "Date" is a reserved word.  I would strongly recommend against using it as a field name in your application.  If you do, you MUST encapsulate all references to that field with brackets [ ].

Author

Commented:
thank you for your help I will go for the function idea

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial