Reports :: Conditional Formatting Two Fields In Report
Mar 26, 2015
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 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.
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.
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.
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.
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?
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
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.
I have a form in datasheet with the fields "Date In" and "Date out" among other.
I want to be able to Change the backcolour of the "Date In" to red, yellow, or green depending on the length of time elapsed between current date and date in. This works fine on the conditional formatting, but now I only want to apply this formatting IF the "Date Out" is empty.
Need to confirm whether the Default formatting option in the Conditional formatting Dialog won't work in datasheet view of a form. Please note that all conditional criteria are working fine but not the Default Formatting option - only in the Datasheet view (In Single Form view the default formatting is working fine)
I am having a problem with the formatting of a report. My boss would like for the report to show (grouped by employee name) a horizontal list like below and update itself.
Each training class has a category in the table showing which model(s) the training is. For OJT I got it to count up the tasks and tasks completed in each model and calculate the percentage. I can easily get it to show vertical for each employee. I cannot get it to show like above. I tried using IIF statements to pull the hours and OJT by model aircraft. If I try to put it in the detail section it shows vertical and if I put it in the group of employee name it only returns one record (I assume its still trying to show vertical). The only way I could think of to get it to show properly was a crosstab query but its my understanding those are for archival purposes or for a snapshot and do not update. This report will need to update as things are entered.
A client wants to convert a report I designed for him, to a word document so he can edit it.
The report's Report.Orientation Property is set to 1 - Right-to-Left
Relevant TextBox.ReadingOrder Properties set to 2 - Right-to-Left,
and relevant TextBox.TextAlign Properties are set to 3 - Right
When exported to RTF, the document is LTR and text alignment is to the left (even though the page actual layout is seems RTL, just like the report).
Is there a way to enforce RTL layout in the exported Word file?
I am using Acc2010.
Edit: Iv'e added a sample word file. You can see it looks RTL, but is actually LTR. Set it as RTL - and it gets all disordered. Now multiply this by 47 pages...
What I want to be able to do is have a button next to every client entry which the user can click. Once the button is clicked, I want the "person name" box in the report to be formatted to have a yellow background.The purpose of doing this is so after a couple of days when we come back to the report, we can easily see by the yellow background which people we have to follow up with.
I don't think conditional formatting will work because I have so many different "person names" in the report that it would go above the 3 rule limit, only solution in VBA.The button I created is called "Format", and this is the VBA code I have tried:
Private Sub Format_Click () Me.Person_Name.BackColor = vbYellow End Sub
I am working on a report and I am having issues changing the coloring of the rows in the report. If possible, I would like the first column to be completely white with no alternating color because there is only data in one cell at the top of each grouping. So the column with "Aggregates", "Attachments", etc would have no alternating color rows below them.
In addition to this, I am having difficulty changing the alternating row color for the other columns. I'd like to use colors with more contrast. I have tried changing the color in both design and layout view, but the options for formatting row color is also greyed out, and not available for me to use. In other words, the alternate row color button on the home tab is unclickable, for lack of a better word.
