Conditional Formatting for One Field Based on Value of a (Different) Date field

kjohns

Registered User.
Local time
Today, 18:42
Joined
Feb 22, 2008
Messages
26
Hi. I'm struggling with how to set the value of my PatientName field based on the patient's referral date (ReferralDate). Both fields are on frmPatientRecordbyLName and I'm sure I'll need to use VBA code since I'll need to set the color to one of five colors and Access2000 is limited to 3 conditions in the Access-defined contols.

I want it to be broken down like this:
If the ReferralDate is between 01/01/2004 and 12/31/2004, then the PatientName field should have the background color set to 15647229.
01/01/2005 - 12/31/2005 - back color 10670331
01/01/2006 - 12/31/2006 - back color 9826295
01/01/2007 - 12/31/2007 - back color 10747051
01/01/2008 - 12/31/2008 - back color 16506532

Could someone help me with the code? And on what event should I put it? When I was searching for similar questions I saw all sorts of references to On_Format events, but that's for reports and my question is for a form.

Thanks in advance for any guidance you can give!
Kate
 
You would put the code in the on current event of the form. Since you are changing the color based on the year, the best way is to use a SELECT CASE statement in conjunction with the datepart() function. You would reference the names of the control on the form using me.controlname not the name of the field. If you use a wizard to create the form, the control name is usually set to the same name as the field name. So the code would go like this:

Select Case DatePart("yyyy", Me.ReferralDate)
Case 2004
Me.PatientName.BackColor = 15647229
Case 2005
Me.PatientName.BackColor = 10670331
Case 2006
Me.PatientName.BackColor = 9826295
Case 2007
Me.PatientName.BackColor = 10747051
Case 2008
Me.PatientName.BackColor = 16506532
End Select
 
OF course, this will only work using a Single View Form; with Datasheet View or Continuous View you're stuck using the Conditional Formatting with it's four formats (three plus the default format) available thru the Menu.
 
RESOLVED - Thank you

jzwp22, that worked perfectly! Thank you for the explanation. The select datepart is a much easier way of doing it than trying to do between...and. Thanks for your help!

Missinglinq, thanks for the watchout. The form I'm working on is in single form view, so it's not an issue for me.

Again, thanks for the help!
 
Glad you were working in Single View! Nine out of ten requests we get for 4+ colors are by people using Datasheet/Continuous View, so I like to warn them up front, so they don't waste time.

Linq
 
Glad to hear that worked for you. Good luck on your project.

And thank you Linq for pointing out the single form versus datasheet/continuous form issue.
 

Users who are viewing this thread

Back
Top Bottom