Reports :: Conditional Formatting Based On Two Fields - Highlight In Red?
Feb 17, 2015
So i have two fields I need to tie together for conditional formatting
If the "status" field says "RETURN" and its over "30" days then I need to highlight it RED
If the "status" field says "AVAILABLE" and its over "90" days then I need to highlight it RED
Is this possible?
View Replies
ADVERTISEMENT
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.
View 4 Replies
View Related
Apr 29, 2014
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.
EX: Contractor Bid Amount Sq Footage =[bid amount]/[sq footage] =[bid amount]/[estimate]
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.
View 11 Replies
View Related
Feb 23, 2015
I want to add a conditional formatting to a combo box to highlight if the value is not on the list.There is a process to read in data from a 3rd party excel sheet that has truck arrival dates and times. My form displays this data and allows the internal users to change it. My form has the "Trucks" field as a limit to list combo box - so basically any data can be read into this field but internal we can only change it to trucks on the list (from the trucks table). I want to highlight where the trucks value is not in our trucks list.
View 2 Replies
View Related
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?
View 12 Replies
View Related
Mar 20, 2014
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:
[colour] = "red"
but nothing happens.
Expression Is [Subtotal]+[Freight] > 50
View 4 Replies
View Related
Jul 24, 2014
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
View 4 Replies
View Related
Apr 11, 2006
Hi all.
I've been struggling to find a solution for this. Never previously used conditional formatting I could use some help.
I have a sub form consisting of continuous forms...
Based on whether or not the below DCount indicates 1 or 0 I want to change the BackColor of a Textbox.
DCount("fID", "tblRegister", "fID = " & Me.fID.Value & " AND samtyckeBarn = TRUE AND samtyckeBarnVuxen = TRUE"))
Where/how do I implement this?
I do not yet fully understand the FormatConditions collection of the textbox but I guess that's where to work.
View 2 Replies
View Related
Mar 28, 2006
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.
Thanks for any suggestions.
Jim
View 4 Replies
View Related
Sep 1, 2005
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.
Thanks
View 4 Replies
View Related
Dec 29, 2013
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.
View 5 Replies
View Related
Sep 5, 2014
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
View 14 Replies
View Related
Aug 7, 2014
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.
View 3 Replies
View Related
Jul 24, 2013
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.
View 2 Replies
View Related
Mar 7, 2014
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.
View 5 Replies
View Related
Jul 3, 2013
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
DoCmd.OpenReport "All_CSCSExpiry_Rpt" acViewPreview
Which has also stopped working. I get an error:
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?
View 4 Replies
View Related
Feb 22, 2015
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.
View 4 Replies
View Related
Mar 24, 2006
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
Can anyone help me out of a ' sticky situation'..
Ta Very much
Carol
View 3 Replies
View Related
Oct 15, 2014
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.
View 3 Replies
View Related
Mar 13, 2014
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.
View 11 Replies
View Related
Jan 8, 2015
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.
View 3 Replies
View Related
Dec 11, 2012
I am struggling to format a field on a report.
I believe it is because special charachters are in the text string
I want to highlight the field if it contains the following text
Stage 6: Won and Deploy (shipping)
Regretably, I believe Access see this as code.
I suspect I need to encapsulate it some way, but nothing seems to be working
What am I missing?? How can I force Access to see it as a text string??
View 1 Replies
View Related
Feb 6, 2013
I have tried to change the colour of the text in time fields with no a value of 0:00 by using conditional formatting - to no avail.
View 4 Replies
View Related
Jul 5, 2014
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
View 14 Replies
View Related
Nov 4, 2014
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?
View 8 Replies
View Related
Apr 27, 2015
I have a field that displays a date on a form.
I set the conditional formatting to be
Value is Between Date() and Date() + 14
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.
View 14 Replies
View Related