Please Help with Report!

[ryanturner]

Hey I'm an intern and the company I'm working for has me designing a database of their inventory. One of my reports has all of their computers with info on them. I have the date they were purchased and want to write code so that if the purchase was over 4 years ago the the text will turn red... after attempting to learn access, vb code, and a jist of C# in the past week and a half this is what I got and it isn't working... My co-workers have taken a look at it but its been so long sense they have used VB themselves they cant really understand what the problem is...



Private Sub Report_Current()

Dim Date1 As Date
Date1 = Report_ComputerReport.DateBought
If DateDiff("yyyy", Now, Date1) > 4 Then
DateBought.ForeColor = vbRed
End If


End Sub



ANY help would be greatly appreciated...

 

[pbaldy]

The easiest way to do what you're trying to do is with Format/Conditional Formatting (on the ribbon starting in 2007). If you want to do it with code, the appropriate event is the format event of the section containing the control being formatted (typically the detail section).

 

[ryanturner]

Hey Paul,
So will I want to make a formatting rule with the expression builder then?

Something maybe like...

current date - date bought > 4

 

[pbaldy]

If you're formatting the control holding the value, you can use Field Value Is and then the appropriate other options. Otherwise use Expression Is and an expression like yours

DateDiff("yyyy", Now(), [DateBought]) > 4

 

[ryanturner]

Thanks Paul! Just figured it out as you posted that!

 

[pbaldy]

Ah good, glad you got it sorted out. Welcome to the site by the way!