Conditional Formatting Expressions


I am using the following conditional formatting expressions. The first and last one seem to work okay but the middle one does not work. What is wrong?

([Intensity])1.2 And ([Intensity])1.3 (Red)



Post your answer or comment

comments powered by Disqus
I was wondering if anyone knows the syntax for doing a odds or even type expression in a conditional format. basicly I have a field that has vaules of 100,200,300,400,... up to 900. what I want to do is hilight all the even numbers and leave the uneven white. if someone knows the syntax that would be great. here is what I have so far but it does not work exactly right
[SizeCampagin] / 200


I have two related tables and a form and subform. The main form [frmTrialInfo] contains data from [TblTrialInfo] and a subform [subfrmChecklist] with data from [TblChecklist]

I want to use conditional formatting to bold a box in the [frmTrialInfo] is a control in subfrmChecklist or TblChecklist is checked No

I have tried the following expressions, however none have worked, can you spot my mistake? Is it possible to format a field in a form from a subform or a table value, as neither approach has worked.

I have used the following conditional formatting on the field I wish to bold, however none of these have worked?



Any ideas if this is possible, or what I might be doing wrong? No luck with the formatting of the form no matter what I have tried!

I have a date field in my form that I would like to have the date change color sixty days prior to the date entered. Can someone give me the expression to use in conditional formatting? For example: If the date is 9/30/05, I would like it to change color on 8/2/05.


Hey, got a quick/easy one.

Entries in my table have a column for 'Project Name'.

On a report, I want to have a select few projects be highlighted in yellow.

In my table, I have a column 'Highlight' with a check box yes/no format.

What is the proper expression to enter into the conditional formatting box?

[Status]![Highlight]=Yes is what i'm trying .. not working out for me.

Thanks for the help!


I have a report that has about 50 fields... I need any fields that have been left blank to be shaded.

I am able to Set a field to shade grey by going into conditional formatting:
Expression Is IsNull([FieldName])
And select Grey from the paint bucket

Is there any way to do this on multiple fields at once, or do i have to go in field by fiield and set it manually.

Thanks in advance

Can I use wildcards on the condition format?

If the field value = 1,?????? then True

I just want tolook at the 1st 2 digits of the field value is this possible?


Hello, i was wondering if anyone could help me out,

I have a form on my database that I require some advanced Conditional Formatting on.

I require my Invoice approver Label & Invoice Approver Textbox to be faded or locked if the Request Type equals 'SSR'.

I have been able to change the colour of the text in the text box using conditional formatting, Expression Is [Request Type]='SSR'

But as of yet I am unable to change the colour of the label.

Any suggestions?

I have a subform on a tab control. I want to add a text box that will be unseen by the user when there are records in the subform but visible when there are no records displaying "No Records Found". I want to do this by using Conditional formatting by having formatting blend into the page unless a condition is met then it will change the Fore color to Red. I've used the code:


to determine if the subform recordcount is 0. It works fine when testing in a messagebox but doesn't work in the conditional format expression.

Hi Guys,

Thanks for the on-going support! I'm trying to do something that seems simple but I'm having a problem!

I have a form (see attached) where there is a box called 'CV Date of Renewal' which has a date in. If the date is in the future, I'd like the 'CV Information' Box to go Green and display Valid.

If the Date has passed I'd like the 'CV Information' Box to go Red and say 'Expired'.

If the Date is due to expire in a month I'd like the 'CV Information' Box to go Yellow and say 'Expires Within One (1) Month'.

There are three such boxes similar to the 'CV INFORMATION' Box, called Q INFORMATION and Z INFORMATION. These two have the same rules as CV INFORMATION, in that there is a box called 'Q Date of Renewal' and 'Z Date of Renewal'. If all three of these boxes are Green and Valid, I'd like another box, called 'Permission' to be Green and say 'Yes'. If there is one or more boxes that are red and 'expired' I'd like the 'permission' box to be Red and say 'No'. If there are yellow boxes, (one month expiry) as long as they appear alongside green boxes permission is 'YES'. However, if they appear alongside Red boxes permission will be 'No'.

I'd like this to be an automatic update, so that if, for example, the CV date of renewal passes the box will turn to Red from yellow, having previously been green.

I did a similar thing in excel but I cant get it to work on access: this is what I have (for the individual boxes - in this case CV):

=IF(CV Date of Renewal>TODAY()+30, "Valid", IF(CV Date of RenewalTODAY(),Z Information>TODAY()),"YES","NO")

I have done conditional formatting for all boxes mentioned, based on the information already on the table, but I'm not sure how to do the parts where the boxes will update themselves based on the change of time. I'm also not sure how this will appear in a table, if needed, or in VBA etc so I'd be grateful for your wisdom!


I am using a continuous form, and I cannot figure out what conditional formatting expression to use to change controls in the ACTIVE RECORD.

For example, continuous form may have:

RECORD1: Field 1 Field 2 Field 3 Field 4
RECORD2: Field 1 Field 2 Field 3 Field 4
RECORD3: Field 1 Field 2 Field 3 Field 4

When I am in Field 1,2,3, or 4 in RECORD2, I want Fields 1,2,3 AND 4 to change color in RECORD2


I would like to know if it is possible to create a conditional formatting expression based on a value that does not have a corresponding control on a crosstab report.

I have an order summary crosstab report that displays the total quantity on order for each of our customers for each product we sell. The tricky part is that we are not authorized to sell all of our product lines to every one of our customers. I would like to shade in gray the (value) controls on the crosstab report if we are not authorized to sell that product to that customer.

If we are authorized to sell a vendor's product line to a particular customer, there is a RelationshipID in our tblRelationships table, with corresponding VendorID and CustomerID. However, I can't include this RelationshipID field in the crosstab query for the report, since I only want to display each product once on the left as a row heading, and each customer name once at the top as a column heading.

Is there a way to accomplish this?

I have a form called "Customers" with a subform called "Investments", the investments subform is based on the "Investments Query". I have 2 fields on the subform for incomes for Client 1 & Client 2. I wish to use conditional formatting to disable the client 2 field based on a relationship of "single" on the mainform. I successfully did this by putting the "relationship" field from "Customers" into the "Investments" query and set the formatting based on an expression for that field.

The problem emerges when I then try and use check boxes or option groups on the sub form. They all seem to be turned off andproduce no result when clicked on.

If I remove the "relationships" field from the query they work again.

I can't work out why this is happening. Any suggestions?
If I can't set it up this way is there a expression i can use on my subform to reference the field on the mainform?
I have tried "[Forms]![Customers]![relationships]=1" but this produces no result. Any thoughts please?

So here's my latest conundrum.

I have a table that keeps track of when particular types of parts need to be rebuilt or thrown away- each part type has a 'caution' and an 'nfg' value (NFG being the term our engineers use for a part that's outlived its usefulness- I'll let you ponder what it means).

I also have forms set up to display individual parts by serial number as well as their total mileage (and various other metrics).

What I'd like to do is have the control (a text box) that displays the total mileage for a given part to change the back color to yellow if the mileage is greater than the caution value in the table, and then if it's past the NFG value, change the back color again to red.

I've attempted to use the conditional formatting tool with a query (setting up sequential rules for caution and nfg) but that doesn't appear to work, possibly because I'm building the expression wrong. I've tested the query and it returns the correct values.

I can just set up the controls directly by entering the mileage thresholds numerically in the conditional formatting tool, but I'd much rather have a table I can quickly edit.

I am using Access 2002. I have a simple database of tasks with due dates. I have a report which shows the tasks and are sorted by Due Date. I wanted to use Conditional Formatting to highlight red the tasks which have a due date past today and yellow the tasks due in the next two weeks. It appears the Conditional Formatting feature in Access only works with specific words or numbers and does not work with code like =TODAY(). The =TODAY() code works fine with conditional formatting Excel 2002, but in Access I get an error message saying "The expression you entered contains an invalid syntax." Can anyone help or offer any suggestions?

All i want to do is to make my Release field a different color if it is new than a certain date....

I have used Conditional Formatting for fields that are common ( formats >>> V, or D, ect. but i dont understand how to use Conditional formatting for a date range

I've tried this: >06/01/02 in the greater than field given for conditional formatttin but nothing happens. . . It looks like i need to use an expression and tried Date( >06/01/02 ) but i get an expression error.

I know, I know .... that code is way off. It just seems so simple, any suggestions.

So far i haven't used any VB on my database (at least anything that i have coded by hand) and still pretty new to Access.


On a report there is a text box customers. I want certain customers to be printed in one colour and otheres in a different colour.

Say I have 4 customers A,B,C,D to show in red and the rest in blue.

I have tried to set in conditional formating but it only works for one customer at a time and there are only 3 conditions allowed. Can somebody advise me how can I do it and if I should be using an expression, what would that be?

Thank you

In my report I have date fields that span over 5 years with different pull dates ex. 3, 6, 9,12,24,36,48 and 60 months. I have successfully formatted these fields using the conditional format by:
Expression Is Date()[6 Month]
for the 9 month field and so forth for each field. This has allowed me to highlight the field that is the next upcomming date. One report I have this works fine for, very simple report. In a more complicated report, I have moved the fields together and have highlighted every other row to segregate them this formatting does not work. It will highlight the field, but it will pull in earlier dates, from 2004. Is there another way to format the fields with conditions other than the condition format? Possible to do it in code similar to highlighting every other line? The example I followed was here:

I will be out and about for a couple of days, but will return any replys to questions when I get back.



I know this should be easy but I can't get it to work. I want to add a conditional format to a report field based on an expression that evaluates two conditions:

([txtCriteria1].[Text]="") And ([txtTypeNum].[Text]=1)

I'm sure this is just a case of syntax. Should I reference the recordset instead of the report fields?

Thanks for your help!


I have a report with values that are sorted in the underlying query to be grouped together if they are the same; i.e. for a column of names: if there are three "Jim" values, they are grouped together. I would also like to use conditional formatiing for the control box in the form to turn the "Jim" entries red. I know how to do this in Excel but it's not quite the same. Wanted to use the Tools, Conditional Formatting function in Access to accomplish this.

Does anybody know what I should use in the "Field Is" or "Expression Is" box of the conditional formatting "wizard"?

Hi I am trying to use the conditional formatting button in the formatting menu of Access 2007.

Putting the format on is similar to earlier versions, and I can do this. However it doesn't seem to work.

What I am trying to do is colour a field if it's value is before today. When I put in the expression it seems happy, but when I show the report in print preview I get no colours.

I have found that when the report is in report view I can see the colour if I click on the field, and it changes correctly as I click on the field in the next record. I only get the colour for the field I have actually selected.

This is obviously no use for printed reports and as I cannot imagine what use this feature could be I assume I'm doing something wrong.

Does anyone know of a global setting or anything else that I could be missing.

All I want is a printed report coloured to show what jobs are late, something it was easy to do in previous versions of access, so I'm sure it is possible in access 2007.

Thanks for your thoughts.


Can anyone tell me why I can not get to a control source on a report? I am tiring to apply conditional formatting to several controls based on the value of another control. I followed the instructions on the Office online site but it doesn’t tell you what to do if it doesn’t work. When I put a break point on a report and try to display the control source for the field I want. I get a message “Database can’t find the field ‘Feild1’ referred to in your expression.” I tried adding a textbox to the report and using that but it still did not give me the formatting I wanted. Any ideas why this is happening and how to fix it.


I need held with a simple expression for conditional formatting. I have two fields - the first is CONTRACT VALUE and the second is CONTRACT TYPE. I want to write an expression that change the colour of the contract value IF Contract Type = "Estimate".

I have never written a statement for conditional formatting and I am stuck. Usually I would write something like IIF([Contract Type]="Estimate"....
and this is where I am getting stuck. Usually I would include an option for if contract type does not equal "Estimate". How do I write a falsepart for conditional formatting?

Hi Everyone,

Second post in this forum ever - hoping to get anyone's expertise.

I have a report setup based on a query, one of the fields contains people's names, for example "John". This query can return multiple instances of the name "John", for example:

Contract AMT | Name
$100 | John
$300 | Toby
$150 | John
$400 | John
$500 | Sarah

Basically, I would like to add conditional formatting to any name that appears more than 2 times, for example.

I need help writing the expression because the normal formatting tools access has all assume the info I am working from is numbers, and that is clearly not the case in this example

Any help would be much appreciated!.

So I have a couple fields I want to change the back color on based off the data entered but I having problems with the negitive numbers.
So if field 1 was between -2 and +2 back color Green
if field was not between -2 and +2 back color RED
0 also seems to screw things up a bit as well.
Is the a way to use ABS in the expression on the conditional formatting ?
If (field1)

Not finding an answer? Try a Google search.