• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

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
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 4
  • 4
2 Solutions
 
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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 FyeCommented:
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
 
PeterBaileyUkAuthor Commented:
thank you for your help I will go for the function idea
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now