Conditional border colour on date field...

[scottmfitzge]

Hi ,

I wonder if someone is able to help me as i am really stuck on this problem with my database. I have created the database in Access 2003. On one of the forms in the database, i want the "Date" field border to be displayed a different colour, dependent on how long the record has been in the database compared to the date in the "Date" field.

If the record has been in the database for < 2 weeks i want the border to be green, if the record has been in the datbase for 2 weeks or more i want the border to show as Yellow, if the record has been in the databse for 4 weeks or more i would like the border to be amber/orange, and if the record has been in the database for 6 weeks or more, red.

First of all, is this actually possible? and if it is possible does anyone know the VBA code that i would have to put in the event procedure, and if so which 'event' would be best to use in this scenario....

Really appreciate your help on this guys, i have always been able to find a resolution on here for my Access problems, but am really stumped on this one .

Cheers
Scott

 

[RuralGuy]

You are probably going to want to put the code in the Current event of your form and the BorderColor property of the control set the color. Your CurrentEvent code will look something like:

Private Sub Form_Current()

Select Case DateDiff("d", Date, Me.YourDateControl)
   Case Is >= 42
      Me.YourDateControl.BorderColor = 255      '-- RED
   Case Is >= 28
      Me.YourDateControl.BorderColor = 4227327  '-- ORANGE
   Case Is >= 14
      Me.YourDateControl.BorderColor = 65535    '-- YELLOW
   Case Else
      Me.YourDateControl.BorderColor = 32768    '-- GREEN
End Select

End Sub

...using YourDateControl name of course.

 

[missinglinq]

Allan's answer is based on the assumption that your form is in Single View. If you're using a Datasheet View or Continuous form, you'll have to use Conditional Formatting from the menu.

 

[scottmfitzge]

Hi guys - thanks for your help here really appreciate it. I have applied the VBA you have provided and it works (well sort of) everything is showing as Green. Now i am not sure if this has something to do with it, but the date field uses the =Now() function so it includes a time as well. Any ideas what could be causing the code to shoot to the 'else' part of the VBA?

 

[DCrake]

Simple Software Solutions

As previously outlined if you are using a continuous or datasheet view then ALL rows will adopt the same color as the first one. To employ the case statement syntax your subform data MUST be in single veiw only.

I do have an alternative solution which I have attached. If it is any good let me know.


CodeMaster:

 

[scottmfitzge]

Yep - this looks really good, i know i am a pain but can you explain how i would go about implementing this, by using the date field as above...? - i have had a play with your example, but i cannot see how you have done it.

 

[DCrake]

Simple Software Solutions

In a nutshell this is how it works

First I created a table that contained a PK, a description of the status, in your example it would be 0-2wks, 3-4wks,5-6wks,6wks+ Then I assigned a colour to each value using the ole object.

Then in my table that held the actual status I stored this as a value FK-PK on a 1:1 relationship. For demonstation purposes I also had a department grouping to show the functionality.

I created a query to link the FK-PK (QryAttendances)
On the subform I defined it as a continious form with the above source data. Then I first brought in the ole field and stretched it across the full width of the detail section. I then placed other fields on top of this and made their backgrounds transparant. The staus Id in the subform was a combo box which showed the status description but had the PK as the bound control.

Like I said playt around with it and take it to bits. Thats the way to see how I did it.

David

 

[scottmfitzge]

Excellent - thanks for your help - really appreciate it - I am going to crack on with this now and let you know how I get on....

 

[missinglinq]

Everything's Green because all the values being generated by Allan's code is a negative number! He inverted the two dates in the DateDiff() function.

Select Case DateDiff("d", Date, Me.YourDateControl)

should be

Select Case DateDiff("d", Me.YourDateControl, Date)

and I think it'll work, assuming as stated before, that this is a Single View form.

 

[RuralGuy]

Hi guys - thanks for your help here really appreciate it. I have applied the VBA you have provided and it works (well sort of) everything is showing as Green. Now i am not sure if this has something to do with it, but the date field uses the =Now() function so it includes a time as well. Any ideas what could be causing the code to shoot to the 'else' part of the VBA?

Are the other posters correct that your form is not in single mode but in either Datasheet or Continuous mode?

 

[scottmfitzge]

Yep they are correct - Sorry I should have told you guys that in my original post - i am using a continuous view form for this list - i am in the middle of trying out the idea from DCrake - i will let you know if this is successful

 

[RuralGuy]

Thanks. We'll keep an eye out for your post.

 

[missinglinq]

Nobody ever wants to do this for Single View! That's too easy!

 

[scottmfitzge]

Hi guys - I have only just had a chance to take a good look at this - ok so far i have got up to the second part however i am now stuck (really sorry guys). Don't quite understand how a store this as a value FX-PK? on a 1:1 relationship in the table and also how i can make the colour change based on the date field rather than a drop down.

Thanks again for all your help guys...

 

[DCrake]

Simple Software Solutions

Right,

In the current combo box on the subform this is bound to the rate field in the table which inrun is linked to the status table in the query. So if you change the value in the combo box this updated the value in the main table. As such the colour changes on the form.

In you example you want it to be date depandant. So what you need to do is to employ some code on the AfterUpdate of the date field. such as:

Dim Elapsed As Integer
Dim nIndex as Integer

Elpased = DateDiff("d",YourDate,Date)

Select Case Elpased
    Case < 14
       nIndex = 1
    Case < 28
       nIndex = 2
    Case < 42
       nIndex = 3
    Case Else
      nIndex = 4
End Select
Me.Status = nIndex

Summary
1. Work out the number of days between the specified date and Today
2. Assign a value based on yourr criteria to a variable (nIndex)
3. Update the field in the table that contains the record status.

By doing so it will automatically change the background to the coressponding colour.

David

 

[scottmfitzge]

Great Stuff! Thanks for all your help guys - i have managed to get this to work this morning nice one!