Conditional Format Based On 2 Fields
Jan 24, 2008
Hello All,
I have searched the forum but I don't quite get what I am looking for.
I have a subform that has these 2 fields:
- Status
- LastFollowup
What I want is if the Status shows "Pending" and the date entered on the LastFollowup is more than 7 days. Then it should turn Yellow.
I tried this code in the expression box but it doesn't seem to work
[Status]="Pending" and [LastFollowup]>7
Could anyone please assist? I know this is simple but it's not working for me.
Thank you
View Replies
ADVERTISEMENT
Feb 4, 2005
I'm having trouble with a tricky conditional format in a continuous subform.
The criteria is based on field [GroupNo] when it changes (increases) which are always listed in numerical order.
Basically all I want to is for the text box colour to alterante (eg blue & yellow)when GroupNo changes value
So it would look something like this....
Group NoID (highlight colour)
1 (yellow)
1 (yellow)
1 (yellow)
2 (blue)
2 (blue)
2 (blue)
3 (yellow)
3 (yellow)
etc etc
btw, although values will always be in order they may not all be output, so it's not just case of odd, even, odd, even (that would be too easy!)
I can retrieve the Previous Record's GroupNo value (via a function) in another text box and compare current GroupNoID with this in the Format Expressions and manage to change the colour, but it won't sustain this new colour for the subsequent same GroupNos, obviously as the values have changed.
So how can I....
Change the colour when the value changes.
Keep this new colour until it the value changes (increases) again.
Many Thanks
View 14 Replies
View Related
Sep 14, 2006
Hi All,
I'd lile to show/hide selected subforms based on the the text in a txtfield.
The text in the selection field (txtSelect) I'd like to use for that may be either one of the following:
book-01, book-02, book-03 etc OR,
folder-01, folder-02, folder-03 etc OR,
record-01, record-02 record-03 etc. OR
it can be just a 4 digit number.
If the txtSelect contains "book" I'd like the subfrmBookdetails to be visible.
If the txtSelect contains "folder" obviously the subfrmFolderdetails etc.
What I'm struggling with is the If-Then statement for selecting the partial text in txtselect.
I've tried things like this:
On current
me.txtSelect.setfocus
if me.txtSelect.text="book*" then
me.subfrmBookdetails.visible=true etc etc.
No joy.
I know how to get this to work in a query with a like-statement but I have not been able to find anything on this in relation to conditional formatting of a form. Any one and good ideas?
Thanks,
Walter
View 2 Replies
View Related
Feb 1, 2005
:D 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
PLEASE HELP!
View 5 Replies
View Related
Jan 14, 2014
I have a field on a form OrderDate and a field DOB. I am not sure how to create an expression in conditional formatting that:
=IF([DOB] - [OrderDate] <30), Then background is red
So if the OrderDate is 1/1/2014 and the DOB is 1/15/2014 then the DOB field turns red
If the OrderDate is 1/1/2014 and the DOB is 2/29/2012 then the DOB field does not turn red.
View 8 Replies
View Related
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 5 Replies
View Related
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 18, 2006
Hi,
I am trying to place some code in the "expression is" section of conditional formatting. Need some help converting from code to conditional formatting format.
I have set up a check box "Check83" next to a standard control box "Title(O)".
Below is the code I am currently using. This code is in the after update section of the check box, and in the oncurrent section of the form.
This code turns the box blue when I click the check box.
Private Sub Check83_AfterUpdate()
If Me.Check83 Then
Me.[Title(O)].BackColor = vbBlue
Else
Me.[Title(O)].BackColor = vbWhite
End If
End Sub
My form has 46 records and all have check boxes linked to the control box with the code above. When the box is blue in record 1, I don't want there to be a blue box in record 2. Can't figure out how to do this. Tried to do in with code, but failed. Thought it may be possible using conditional formatting.
Thanks in advance for your help
Jcwishart
View 3 Replies
View Related
Feb 23, 2005
Because they are more efficient, I am using hyperlinks to connect my forms. I want to be able to deactivate hyperlinks based on criteria that are selected.
Please help! :o
View 3 Replies
View Related
Dec 2, 2013
I have a report with a list of employee names (and other information). A handful of them need to be a different color (other than default). There will always be more than 3, so I can't use the conditional formatting tool.
View 14 Replies
View Related
Dec 7, 2005
Quick question.
I have a continuous form in Access 2003 listing products for return for a customers. I am in the process of creating a completion form which will just have the product number, model number and then a command button called complete.
What I'm doing is have it so when the person clicks the button it adds a value to a field saying Complete. OK. But, what I then want it to do is for any record thats associated with this customer where the field = complete to have the command button gray out.
Is this possible? I know I can conditional format a text box to do this but can't see a way to do it with a command button? Any ideas?
I have though of layering a transparent button and having a textbox behind this with complete in and having this change colour but not sure if thats the best way?
View 1 Replies
View Related
Mar 24, 2006
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 whats input 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 form background changes to the required colour when its value is 'set exactly' or matches, but what I am trying to achieve is to change it according to the first letter of entry regardless of what follows thereafter? 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
Your help would be gratefully appreciated:confused:
Thanks Very Much
Carol
View 1 Replies
View Related
Feb 17, 2014
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
View 2 Replies
View Related
Dec 11, 2013
I'm exporting a query to Excel, and I want to be able to conditionally format certain rows of the export using Access VBA. Is this possible?
View 1 Replies
View Related
Jun 17, 2015
I have a report and i'm trying to set up the conditional format to highlight any dates within January red.
View 1 Replies
View Related
May 20, 2014
I have a short date field 5/20/2014
I would like to set up a conditional format to format those fields in the current month. For example, this month is would format all fields with "5".
Next month all fields with "6"
In the month space.
I can't seem to figure out the expression, I know I'm close.
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
Feb 28, 2014
I have a main form with several subforms within it. These subforms are navigation tabs. In the footer of the main form I have a textbox that I want to hide unless the 2nd tab is selected.
Details:
3 Tabs, 1 subnavigation subform, 3 tab indexes(0,1,2)
tab index 1 the textbox on the main form(parent) should be visible. tab index 0 and 2 the textbox should be hidden.
I was thinking I could do something like when tab index 1 is onFocus then textbox = visible and vice versa on the other tab indexes?
View 14 Replies
View Related
Dec 22, 2006
I'm not new to object oriented programming but I am new to Access. I have found some questions similar to mine in search but I need more explanation if someone wouldn't mind.
In my form, I have a combo box that looks up 3 values from another table. Among other controls, I have a few text boxes that I only want visible if 1 of the 3 possible combo box options are selected.
I understand that the code for this should probably be placed in the "events" of the of the combo box...which one? I understand the logic but what would the code look like? Do I use the expression builder?
Thanks for any help on this.
View 1 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
Aug 12, 2011
I have text boxes that combine two fields into one, for example the control source is =IIf(IsNull([BuckC])=True And IsNull([BoostC])=True,"",[BuckC] & " , " & [BoostC]) . For simple its just =[Buckc] + ": "+[BoostC]. I am trying to set a condition that if one of them exceeds a certain number the background color will change to yellow. I know how to do this if its only for one field but I am wondering if it is possible to do it with multiple fields?
View 9 Replies
View Related
Mar 27, 2014
I have the following code that I need to modify:
Code:
Private Sub ChargeReport_Click()
On Error GoTo Err_ChargeReport_Click
Dim stDocName As String
stDocName = "Charges_Report"
DoCmd.SendObject acReport, stDocName, acFormatPDF, , , , "Charge Sheet"
[Code] ....
Currently, this code opens an input box that accepts the ID number for a particular record. Then it attaches a specific report for the selected record to an email. It works fine for this purpose.
I want to modify it so that it attaches one of several different reports depending upon the value of a [Staff_ID] field in the selected record.
I've tried a number of different solutions using an InputBox to get the record ID along with an If/ElseIf/Else construct that evaluates the [Staff_ID] field in order to determine which report to attach to the email, but I cannot find my error.
View 4 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
