Change color on date field in form for re-certification

My main form has two different date fields on it. One date field needs to be 
re-certified every 6 months, and the other field needs to be re-certified 
every 12 months. I would like each of those fields to automatically change 
color depending on how much time as elapsed since that training.  For the 6 
month field, months 1-5 should be green, 1 month left should be yellow and 
anything expired should be red. For the 12 month field, months 1-11 should be 
green, 1 month left should be yellow,  and anything expired should be red.

Thank you in advance for your help!
0
Utf
4/14/2010 2:29:02 PM
access.formscoding 7493 articles. 0 followers. Follow

5 Replies
753 Views

Similar Articles

[PageSpeed] 46

messingerjc wrote:

>My main form has two different date fields on it. One date field needs to be 
>re-certified every 6 months, and the other field needs to be re-certified 
>every 12 months. I would like each of those fields to automatically change 
>color depending on how much time as elapsed since that training.  For the 6 
>month field, months 1-5 should be green, 1 month left should be yellow and 
>anything expired should be red. For the 12 month field, months 1-11 should be 
>green, 1 month left should be yellow,  and anything expired should be red.
>

Try setting the text box's fore or back color to Red as the
default situation.

Then use the Format menu - Conditional Foramtting.  Select
the Expression Is: option and use an expression like:
	DateDiff("m", [date field], Date()) <= 5
and select green as the fore or back color.

To get yellow for the 6th month, add another condition like
the above with the expression:
	DateDiff("m", [date field], Date()) <= 6

-- 
Marsh
MVP [MS Access]
0
Marshall
4/14/2010 4:49:54 PM
Sorry for the delay in responding back, I've been extremely busy at work. 
That worked! Outstanding! Thank you for your help!

"Marshall Barton" wrote:

> messingerjc wrote:
> 
> >My main form has two different date fields on it. One date field needs to be 
> >re-certified every 6 months, and the other field needs to be re-certified 
> >every 12 months. I would like each of those fields to automatically change 
> >color depending on how much time as elapsed since that training.  For the 6 
> >month field, months 1-5 should be green, 1 month left should be yellow and 
> >anything expired should be red. For the 12 month field, months 1-11 should be 
> >green, 1 month left should be yellow,  and anything expired should be red.
> >
> 
> Try setting the text box's fore or back color to Red as the
> default situation.
> 
> Then use the Format menu - Conditional Foramtting.  Select
> the Expression Is: option and use an expression like:
> 	DateDiff("m", [date field], Date()) <= 5
> and select green as the fore or back color.
> 
> To get yellow for the 6th month, add another condition like
> the above with the expression:
> 	DateDiff("m", [date field], Date()) <= 6
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 
0
Utf
4/16/2010 2:21:02 PM
I take it back. That worked for the 6 month re-certification, but I ran into 
a problem with the 12 month re-certification. Because of that coding, Access 
isn't looking at the year, it's looking at the month, therefore, it's calling 
Feb. 09 the same as Feb. 10 and comparing it to the current year and is 
returning the field as being good since it's less than 12 months ago.

"messingerjc" wrote:

> Sorry for the delay in responding back, I've been extremely busy at work. 
> That worked! Outstanding! Thank you for your help!
> 
> "Marshall Barton" wrote:
> 
> > messingerjc wrote:
> > 
> > >My main form has two different date fields on it. One date field needs to be 
> > >re-certified every 6 months, and the other field needs to be re-certified 
> > >every 12 months. I would like each of those fields to automatically change 
> > >color depending on how much time as elapsed since that training.  For the 6 
> > >month field, months 1-5 should be green, 1 month left should be yellow and 
> > >anything expired should be red. For the 12 month field, months 1-11 should be 
> > >green, 1 month left should be yellow,  and anything expired should be red.
> > >
> > 
> > Try setting the text box's fore or back color to Red as the
> > default situation.
> > 
> > Then use the Format menu - Conditional Foramtting.  Select
> > the Expression Is: option and use an expression like:
> > 	DateDiff("m", [date field], Date()) <= 5
> > and select green as the fore or back color.
> > 
> > To get yellow for the 6th month, add another condition like
> > the above with the expression:
> > 	DateDiff("m", [date field], Date()) <= 6
> > 
> > -- 
> > Marsh
> > MVP [MS Access]
> > .
> > 
0
Utf
4/17/2010 9:38:01 PM
It's looking at the year alright, but is not looking at
partial months.

If you want to know the difference in full months for one
date relative to another date, try using an expression like:

DateDiff("m", dateA, dateB) + Int( Day(dateB) < Day(dateA) )
-- 
Marsh
MVP [MS Access]


messingerjc wrote:
>I take it back. That worked for the 6 month re-certification, but I ran into 
>a problem with the 12 month re-certification. Because of that coding, Access 
>isn't looking at the year, it's looking at the month, therefore, it's calling 
>Feb. 09 the same as Feb. 10 and comparing it to the current year and is 
>returning the field as being good since it's less than 12 months ago.
>
>"messingerjc" wrote:
>> Sorry for the delay in responding back, I've been extremely busy at work. 
>> That worked! Outstanding! Thank you for your help!
>> 
>> "Marshall Barton" wrote:
>> > messingerjc wrote:
>> > >My main form has two different date fields on it. One date field needs to be 
>> > >re-certified every 6 months, and the other field needs to be re-certified 
>> > >every 12 months. I would like each of those fields to automatically change 
>> > >color depending on how much time as elapsed since that training.  For the 6 
>> > >month field, months 1-5 should be green, 1 month left should be yellow and 
>> > >anything expired should be red. For the 12 month field, months 1-11 should be 
>> > >green, 1 month left should be yellow,  and anything expired should be red.
>> > >
>> > 
>> > Try setting the text box's fore or back color to Red as the
>> > default situation.
>> > 
>> > Then use the Format menu - Conditional Foramtting.  Select
>> > the Expression Is: option and use an expression like:
>> > 	DateDiff("m", [date field], Date()) <= 5
>> > and select green as the fore or back color.
>> > 
>> > To get yellow for the 6th month, add another condition like
>> > the above with the expression:
>> > 	DateDiff("m", [date field], Date()) <= 6
0
Marshall
4/17/2010 11:48:57 PM
messingerjc wrote:
> I take it back. That worked for the 6 month re-certification, but I
> ran into a problem with the 12 month re-certification. Because of
> that coding, Access isn't looking at the year, it's looking at the
> month, therefore, it's calling Feb. 09 the same as Feb. 10 and
> comparing it to the current year and is returning the field as being
> good since it's less than 12 months ago.
>
You asked for months and that was the solution given.

I use calculated fields to get the expiration date and use  60  days before 
the event for color coding.
The field turns yellow xx days before and red when it expires.

That's handy but I also have a report that lets me fill in any number of 
days before expiration and one that just shows expired. 


0
Mike
4/18/2010 6:00:01 AM
Reply:

Similar Artilces:

Changing the hotspot of a cursor
I am using a custom cursor, which is a target. How do I make sure the hotspot for the cursor is the center of the target, not the edge? Sam Normally, you would use a cursor editor to design the cursor and specify where the hotspot is. -- Jonathan Wood SoftCircuits http://www.softcircuits.com Available for consulting: http://www.softcircuits.com/jwood/resume.htm "Sam Carleton" <scarleton-nospam@miltonstreet.com> wrote in message news:sb3Vc.23497$Nl1.5836@fe1.columbus.rr.com... > I am using a custom cursor, which is a target. How do I make sure > the hotspot for the ...

Calender Fields
How can i create a date field where the user defines the date using a calender. Hi Faiz, Go to settings/customizations/ select the entity you want to add the field to. Click on attributes/ click new/after you give it name select the attribute type/ select date/time. Save and close\ publish Regards, Jarrett http://www.dynamics.com ...

Setting a date in the Header
I have a report that I need to print every Friday, but the report is for the following week. So, I want it to say "For the week of: Monday, November 5, 2007." How do I calculate the date to be next Monday? IF you run the report on Friday (and never on any other day), -- Add a control to the report -- Set its source to = "For the week of " & Format(DateAdd("d",3,Date()), "Long Date") If you run the report on any other day of the week this will fail. So you might want to use = "For the week of " & Format(DateAdd("d&...

Launching Form from another Form
Hi guys, I am sure there are a few ways, but what is the preferred way to launch a Form from another? When my user saves a new Case, I want to pop up the Contact form. -Ernst Ernst Kuschke (C# MVP) wrote: > Hi guys, > > I am sure there are a few ways, but what is the preferred way to launch > a Form from another? When my user saves a new Case, I want to pop up > the Contact form. > > -Ernst FWIW, a "Contact Form" can be displayed by navigating to the aspx page at "./SFA/conts/edit.aspx?id={contactGuid}" -Ernst ------=_NextPart_0001_08B36A04 Con...

Change default currency of Opportunity when convert Campaign Respo
Whenever we convert the campaign response to Opportunity, the currency on the Opportunity is always base currency and it cannot be changed. Is there anyway to make this auto created opportunity has another currency?? The transaction currency in Opportunity is drived from the original campaign from which you created campaign response. If you want your Opportunity has other currency, you need to set it when you create your campaign from the very beginning. "dorekofu" wrote: > Whenever we convert the campaign response to Opportunity, the currency on the > Opportunity is a...

Change in restriction needs
I was not given correct information. The internal user wants to refuse mail from an external sender. Otherwise the senario is the same. We just need to block an external mail address for one particular internal user in SBS2000. pse help. You could do this globally using System Manager otherwise, create a mail-enabled contact for the external user, add it to the internal users Delivery Restrictions tab then hide the contact from the GAL. "Lane Hoskins" <lane@automatedhorizons.net> wrote in message news:#JspudoQEHA.3616@TK2MSFTNGP09.phx.gbl... > I was not given correct i...

changing the icon in the topleft corner of an applcation
Hi, How do you change the MFC icon in the top left corner of an MFC applcation? I've edited the icon IDR_MAINFRAME, and this makes my icon appear on front of a file, but doesn't change the top left icon. Hamish Dean, PhD ShapeShifter Technology Ltd www.shapeshifter.net.nz + 64 3 377 3140 hamish@shapeshifter.net.nz You have to change both sizes, 32x32 as well as 16x16. Size of the icon appears as a choice in resource editor. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Hamish Dean" <h.dean@xtra.co.nz> wrote in message news:pm0Ra.80775$JA5.1637431@news.xtra.c...

Re: Outlook 2007 create background
I played around with this further. You can still use any HTML editor to = create stationery and place it in the user's Stationery folder. You can = also use FrontPage 2003 to create new themes with background images. = Details at = http://turtleflock-ol2007.spaces.live.com/blog/cns!C1013F1F9A99E3D8!230.e= ntry --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =...

change the formula by changing contents of cell
I have a table that ranks a list of players by their statistics, for example, when I change cell B2 to "walks", my formula (large) lists the player with the most walks in cell B4, 2nd most in B5, 3rd in B6... that works well because more walks are good. But, when I change cell B2 to "strikeouts" I want it to list the player with the fewest number of strikeouts in B4, next fewest in B5 and so on. Is there a way to change the "large" function to the "small" function within the formula? I have set up a helper cell (C2) that changes fro...

Calculating dates
A quick question before i'm off to bed :) I got several dates, all in the future. I'll just give an example, cause it'll be hell to explain otherwise :p I have a date (Belgian format) 26-07-2008 (= July 26th, 2008). Now I need to know how many days have past from 26-07-2004 untill now. So, day and month stay the same and year needs to change to the year before the current one. Then count the days untill the date now. I need this to calculate accrued intrest, should someone wonder (or if that makes it easier to understand ;) ) Preferably, a short function (if possible) since it...

Auto Update in Forms
Please help. I am totally terrible in Access and need simple instructions. I have a form that all it has is the employee name control and the employee id number control listed like this: requestor (employee name) tag number (employee id) Can someone give me a simple method for putting in the requestor name and the tag number will pop up automatically. Created a query called qrytaguser which has both fields in it to try to make this happen. Read several forums, but I don't understand VBA code to translate the suggestions. Thanks in advance. -- Johnny ...

My forms will not work with my email when I click submit.
Sending Form Data via E-mail, it will open up my Email but, the data is NOT contained in the body of the e-mail message. The e-mail address I entered for the ACTION attribute shows up. BUT NOT DATA IS CONTAINED IN THE BODY. ***************************** <FORM METHOD=POST ENCTYPE="text/plain" ACTION="pamjck@juno.com"> Name: <INPUT TYPE="text" NAME="name"> <INPUT TYPE="submit" VALUE="submit button"> ******************************** why is my Email NOT working anymore and adding the data to the body of the e-mail ...

Problem with a date and concatenation
I got some data out of one of our in house databases and it comes out as 01/022206, for example..a missing second "/" (we need to FIX that damn databse too) I have thousands of these rows, and I need to do some analyses by date, so I need a correct date. So I was going to use text to columns> fixed width and separate the 01/02 from the 2006, then I would concatenate with (replacing the actual contents with the cell name of course) ="01/02"&"/"&"2006" But when I do the text to columns, I get the 2006 but I I don't get 01/02.. I get a...

How Do I Change Where Outlook Store It's Information?
Greetings, I have several profiles in my Outlook 2000. For some reason two of them are using the same .pst file. Is there somewhere in Outlook that I can change the default .pst file name where that profile stores information? Any help would be most appreciated. TIA -Minitman Change the pst-file name or location to anything or anywhere you want. When you start Outlook it will prompt you for the file. -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully custom...

How do I change the value in cell based on a future date
I would like certain cells to be cleared after a certain date one way: Put this in the ThisWorkbook code module (Right-click the workbook title bar and choose View Code): Public Sub Workbook_Open() If Date > #12/15/2004# Then _ Sheets("Sheet1").Range("A1,B2,J10,K39").ClearContents End Sub Note that this won't work if the user opens your workbook with macros disabled. Note also that there's no way to prevent a user from accessing your data - i.e., they can set their clock back and open the file. You can make things more difficu...

"Changed by" Column is empty
Outlook 2003/Exchange 2000 Hi, in my mailbox, the "Changed by" column remains empty throughout all folders. When I change to my Team Mailbox, the "Changed by" column has the desired information. Even when I move a mail from the Team Mailbox to my mailbox, the "Changed by" column remains empty. Is this a bug or I am doing something wrong. Thank you very much. Christoph ...

shortcut ket to change formatting of text
hey, whats the shortcut key to change numbers to dates, and vice versa. cheers : -- Message posted from http://www.ExcelForum.com Hi! This may be the sort of thing you want: CTRL+SHIFT+# Apply the Date format with the day, month, and year. This is a quote from Excel Help ( use "Keyboard shortcuts" for th search) Al -- Message posted from http://www.ExcelForum.com ctrl-1 will pop up the Format|cells dialog ctrl-~ (ctrl-tilde, aka ctrl-shift-backquote on my USA Keyboard) will format a cell as General ctrl-# (ctrl-shift-4) will format as d-mmm-yy Try searching excel's...

Changes to requested ship date in SOP should roll down to lines
When changing the requested ship date at the header on open SOP orders you should have the option to roll this change down to existing SOP lines. -- Jim@TurboChef ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/News...

Transfering a changing cell value progressively through a workbook
I am creating a payment application form in excel 2007 using windows 7. Each worksheet represents one months invoice. Say I have a formula in "sheet 1/ cell Q7" that sums the total billed to date for a particular budget line item. This value will be transfered to a the next months payment application "sheet 2/ cell K7" this becomes the total amount of previous applications, then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, and so on and so on, until the completion of the job. How do I acomplish this. -- Thanks Mike ...

Updating all Fields in Word Document
I need to update all fields in the Word document. I have some of the fields in drawing canvas too. I have tried this, which works fine, but except drawing canvas. http://word.tips.net/Pages/T003879_Updating_a_Field_in_a_Text_Box.html Thank you -- Regards Jan Kratochvil Windows Vista Business SP2, Office 2007 SP 2 Sub UpdateAllFields() Dim oStory As Range For Each oStory In ActiveDocument.StoryRanges oStory.Fields.Update If oStory.StoryType <> wdMainTextStory Then While Not (oStory.NextStoryRange Is Nothing) Set oStory = oStory.NextStoryRange ...

recieving reminders since re load of exchange 2000
I have recently done a reinstall of exchange 2000 using setup and selecting reinstall all components. Before doing this I did an offline backup of mdbdata. After reinstall moved backed up mdbdata back. All is working okay all emails and public folders are as before. However a few users are complaining that they are recieving reminders from months ago. Any ideas all I can see is the outlook.exe /cleanreminders to fix it. Andrew ...

Preventing users from saving forms into unprotected formats
I need to stop users from saving forms into unprotected formats, e.g. RTF (believe it or not, my users like to mess about with form layout, creating business interoperability problems) I have successfully intercepted the FileSave and FileSaveAs commands. I also need to intercept the Save As dialog which comes up for a new dialog when the file is closed, so I'm using a BeforeDocumentClose event handler. I would like them to be able to choose not to save, so I give them a YesNoCancel message, but I can't prevent the regular message coming up if they click No on my message...

Updating a form after using a workflow rule to update the entity.
I have a workflow rule that, on create of a contact, calls an assembly and updates an attribute of that assembly. It works a treat, but after saving, the attribute does not appear in the form. I have to close and re-open the contact to see the attribute. Is there any way to have this display immediately? Thanks Saira ...

Fully qualified domain name change
When I first installed Win2003 and Exchange we didn't have a fully qualified domain name yet so the installation of Exchange used the email format of "sender@company.local". While this is OK for internal email external email cannot recognize company.local. We now have a fully qualified domain name so how does one change the current "company.local" email addresses to "company.com" addresses? Thanks and I apologize in advance if this is a stupid question. -- Gyro You need to 'apply' a new RUS policy to add the new e-mail addresses to each mai...

perpetual dates
Is there a way to have excel automatically add a new date to the end of a column each day? My goal is to have an automatic date in column A and daily closing prices for the S&P500(in column B) corresponding to those dates that are updated via web query. Thanks so much! Maybe... Option Explicit Sub auto_open() Dim LastCell As Range With Worksheets("Sheet1") Set LastCell = .Cells(.Rows.Count, "A").End(xlUp) If LastCell.Value = Date Then 'already opened and populated 'do nothing Else With Las...