Reports :: Conditional Formatting On Multiple Fields Based On Two Criteria
Sep 24, 2014
I have a report which I would like to apply conditional formatting on multiple fields. I would like the conditional formatting to be based on two types of criteria
First criteria would be contracts that start with the year 2014. I used the following expression which worked - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Joseph Steinbok" or "Adam Godson")
To this expression I would also like to highlight fields which contracts start with 2014 AND have a particular Director assigned to it. For this I used the following expression - And [DirectorInCharge]="Name"
On their own, both expressions are working but I want to combine them. How do I do this? I've tried the following - InStr(1,[ContractNr],"2014" AND [DirectorInCharge]= "Name") but then nothing is highlighted. I also tried InStr(1,[ContractNr],"2014") AND [DirectorInCharge]= "Name" - in this instance EVERY record was highlighted.
I have a field on a report that I was able to make bold using conditional formatting. I want to make the other fields in the row bold as well if the value of the 1st field equals a set value.
I put conditional formatting on the Contractor field
EX: Field Value Is equal to "Estimations"
I was Bid Amount, Sq Footage, =[bid amount]/[sq footage] and =[bid amount]/[estimate] fields to also be bold if the Contractor field is equal to Estimations.
In my Access 2010 report, I am attempting to perform conditional formatting of one field based on the difference between two fields. In particular, one field is "Time Scheduled" and the other field is "Time In". If the "Time In" is equal to or greater than 60 minutes of "Time Scheduled", I would like to format it to be red font. If it is less, I would like to format it to be green font.how I can conditionally format to do this?
I am trying to add conditional formatting to a several fields in a report. So far i can use the contional formatting to set a field called [colour] to set that field to red and bold. But i believe I need to use an Expression for all fields, so I can make all fields that = red go red. I have used the the expression:
I have a report that I would like to have the rows be a specific color based on the value in 1 field on the row.
I have attempted to use conditional formatting but it will not work (IE, I can get font color to change, but not field boxes to have color).
The field is labeled [text144] based on the value in the field (1,2, or 3) I would like the field to be a different color. i.e. 3 = green 2 = yellow 1 = red
Is there a way to apply conditional formatting on a field based on the contents of another field?
For example, lets say I have the following fields
START_DATE APPROVED_DATE CAT# DAYS_OPEN
The DAYS_OPEN field is automatically calculated in the query and is not a stored date.
My criteria is that if CAT# is 3, my days open should not exceed 30 but if my CAT# is 1 or 2, my days open should not exceed 60. I want to apply conditional formatting so that it turns yellow at approximately 80% of the date limit and red at approximately 90% of the date limit, otherwise the conditional formatting is green.
I know how to do conditional formatting, but no sure if it can be modified based on the content of another field.
I am in the learning phase for MS Access reports. I have a datasheet which contains the employee daily activities.I need a report when either or both criteria is selected. For ex..criteria1 is Start date and End Date and criteria2 is Employee Name.Output of the report should be based on the selection of the criteria ,as I said either or both.
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()<[9 Month] and 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: http://support.microsoft.com/kb/q210392
I will be out and about for a couple of days, but will return any replys to questions when I get back.
however conditional formatting is simply not working. I have two fields on a report, the report is based on a query, and the two fields are called "ExpectedCost" and "ActualCost". I want "ActualCost" to turn red if and when it is greater than "ExpectedCost". I clicked on "ActualCost" and then clicked on "Conditional Formatting". I selected "Expression" and then put the following expression in the space available: [ActualCost]>[ExpectedCost]
The syntax seems to be correct; however it just isn't working.
I wish to use the conditional formatting that if the date required has passed the current date - i wish for everything for that record be turned red when searched or printed.
ive managed to make the Date Reqd turn red but i dont know how i will make the other cells follow the conditional formatting from another cell?
My other Cells i wish to change to red from the Date Reqd are - Customer Code, Site, Plot No, Product
I am making a planner in the access 2010 report. I am showing which activity finished when but I also want to highlight/ shade the cell to the corresponding month on the planner to the right. See picture above. I tried conditional formatting with date rage 1/1/2014 and 31/1/2014 but it does not gives me accurate result.
I have a report where I read the value of a field in my query (a boolean) field, and if the value is true, I display two lines on my report, hide the lines if the value is false. Here's the code:
Private Sub Report_Page() If Me.Flagged.Value = True Then Me.Line102.Visible = True Me.Line95.Visible = True Else Me.Line102.Visible = False Me.Line95.Visible = False End If End Sub
This code works great.... except on the first page of the report! If I run the report in break mode, I can see that it evaluates my condition as "False" on the first record, regardless of the actual value in the field.
My report is based on a query that has some conditions in it, but, everything works correctly - except page 1.
I'm using Access 2010. I set up conditional formatting in some areas of my report. They're very simple like if a cell is between 0 and .4, then the background color of the field is red. They work just fine...when I click on the field. I don't remember ever running into this issue in the past. I've been reading potential solutions on the web, but haven't found a way to make the conditional formatting rules kick in when the report is loaded.
I have a text box on a report from the Control Source CSCS Expiry Date. The format in the table and query and report is Short Date. The Text Format is Plain Text.
I have named the text box txtCSCSExpiryDate. I would like to make the background red when the date is 01/04/2013. I have ensured the text box back style is normal.
I am using expression [txtCSCSExpiryDate]=#01/04/2013#
And in case it was the Control Source: [CSCS Expiry Date]=#01/04/2013#
I also tried: [CSCS Expiry Date]<Date() [txtCSCSExpiryDate]<Date()
I applied the formatting each time, tried closing and re-opening the database. Changed the background format colour to green instead of red and even restarted my computer and it just stays exactly the same.
I've just noticed something else. I have a screen only button on my report header with
The expression On Click you entered as the event property setting produced the following error: A problem occurred while Employee Details was communicating with the OLE server or ActiveX Control.
Is this linked to my formatting not working?
I've just checked and on my 5 reports all of the print buttons are giving me this error! (I added the button as they are part of a navigation form menu which only shows report view, I am editing in the original report design view though.) They were working yesterday! Is it because I compacted and repaired the database?
I am playing with a report. Crosstab report works, but I would like to print check mark if value is "X". Other values are "A" and "E" and they need to remain as they are.
Hello, Could some kind person out there help me out please? I have been 'dabbling in' the area of conditional formatting, particularly in the area of changing the colour of my active form based on the result of what’s output on a text box. I have had some success in this area but where I am getting confused is getting the colour of my form to change on the basis of what is entered into a text box control that does not match exactly. My forms background changes to the required colour when its value is ‘set exactly’ but what I am trying to achieve is to change it according to the first letter of entry regardless of what follows? It always starts with a letter, either a ‘D’ or ‘V’ to indicate whether its ‘Vital’ or ‘Desirable’ and is subsequently followed by no more than 6 numbers and finally a letter. Basically I need my form to change colour based on the whether it is either a ‘V’ or a ‘D’ irrespective of what follows???
What I have been using so far is:
Private Sub Model_Number_AfterUpdate() If Me.Part_Number = "V111145K" Then Me.Section(acDetail).BackColor = 12632256 'Light Red Else Me.Section(acDetail).BackColor = 8421631 'Blah Gray End If End Sub
Private Sub Form_Current() If Me.Part_Number = "V111145K" Then Me.Section(acDetail).BackColor = 12632256 'Light Red Else Me.Section(acDetail).BackColor = 8421631 'Blah Gray End If End Sub
I have a form which basically has - employee, work done, hours and a calculated field (w/hour) of work done/hours
I can manually set the conditional formatting however as time changes targets will go up and it would be easier to update the targets in another 'admin form' (i can manage that part)
Its just referencing the other table to say the likes of :
if work per hour is greater than tbltargets.convtargetmid then vborange, if work per hour is greater than tbltargets.convtargethigh then vbgreen else vbred.
I have a subform (called NAMES) on a main form (called MAIN) that lists names. Elsewhere on MAIN, I have a second subform (called ASSIGNED) that lists people that have been assigned.
When I double click a name on NAMES, it automatically adds them to the ASSIGNED list (who's record source is a table). What I want the NAMES form to do is conditionally format the NAMES form so that the record that's been added turns green. That way I can see who I've added and who I haven't yet added. Once I've added them all, the NAMES form is a simple list of all-green names. If there's a black name in the list, it's because I haven't yet added them to ASSIGNED.
I've tried a DLookup function, and I've tried a Value= conditional formatting rule, and neither one are working... I already got the 'double click to add' thing working, now I just need the name to turn green when I've added them.
I am trying to get the text in a couple text boxes to turn green if a certain Yes/No field is left blank.
I know the conditional formatting is working, because condition 1 and 3 are working fine (those expressions are referencing text fields).
I would like to have Condition 2 look at [DM_Approval] from the bound query and if it is unchecked, execute the conditional formatting.
I've tried all kinds of statements, but i'm starting to think that the expressions in the conditional formatting window just won't work when referencing a yes/no field.
Specifically (at the very least) i know i tried [DM_Approval]=False, [DM_Approval]=No, [DM_Approval]=0, and [DM_Approval] is null.
What I am trying to do is create conditional formatting to colour fields within a form.
The conditional formatting option built in allows me to colour based on set values i.e. Value=Design turns green. I need to somehow say if Value contains the word Design turn green. This is because in addition to the word Design there will be a variable description. I tried editing the conditional format created by the wizard to Value="*Design*" but that didn't work.
Now I'm trying to write it in VBA code but am still struggling. Here's what I tried but it doesn't like it:
If InStr([Forms]![Query2]![Activity] Like "*Design")>0 Then [Forms]![Query2]![Activity].BackColor = vbGreen End If
I have a form with 2 sub forms which have identical fields from different records. I want to use conditional formatting to indicate a mismatch between 2 of the identical fields. I have been told I should use [NewField] <> [OldField] but I don't know how to complete the syntax. If I call the first sub_form_1 and the second sub_form_2 and the field name is path_length on both, how do I compile the statement?
It would only apply the formatting for dates that were equal to the date and +5 days.
I then tried Between Now() and Now() + 14
Same results
I then tried Between Date() and DateAdd("d",14,Date())
No change
I checked the property for the box and the back style is normal.
I am making the back color a light red. I tried to change the fore color red. Nothing.
I displayed what Date() was giving me in a text box control and I displayed what Date() + 14 was giving me in a text box control. They both were showing the correct dates, but my date, which was in between them, was not changing colors.
