access field conditional formatting

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
PeterBaileyUkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
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
0
PeterBaileyUkAuthor 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
0
als315Commented:
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?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

PeterBaileyUkAuthor Commented:
your sample is great maybe i just need merge them, the brighter standard green
0
als315Commented:
If you can upload your form, I can try to modify it
0
PeterBaileyUkAuthor 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
0
als315Commented:
Try this. Cancelled - Yellow, Cancelled today - Red, other - Green
CourseTracker--ee.accdb
0
Dale FyeOwner, Developing Solutions LLCCommented:
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 [ ].
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
thank you for your help I will go for the function idea
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.