formatting/filtering dates

[ChrisB37]

:chomp:
I am trying to get fields highlighted when they are due. In my report I have dates listed by month they have been on stability. I need to highlight the date when the month/year is current. Example in the report the 12 Month column is all due in Oct of 2005, I would like for it to be yellow to designate it different from the other date columns. I have tried the conditional formatting with:

field value is between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),31)

This works well for everything, except that it is not specific to the current year only. It will pull dates in Oct 2006 as well.

I would also like to generate a report that will use a similar filter to add to my queries that would only print the due dates. I can insert this same code, but need to refine it to only show the month due, not every pull date for the particular record. So on a new report I can have Lot XYZ with pull dates for only 12 Month, which is the current month.

Thoughts?
Please help, I am in a time cruch.
Thanks.

 

[GumbyD]

Well - I took a look at your database - the design is not what I was used to, but I think you can use the following code to achive what you want. You will need to put an Event Procedure on the on Format event of the detial section of your report and then add this code:


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsDate([12 month]) Then
If Year(CDate(Me.[12 month])) = Year(Date) And Month(CDate(Me.[12 month])) = Month(Date) Then
[12 month].BackColor = vbYellow
Else
[12 month].BackColor = vbWhite
End If
Else
[12 month].BackColor = vbWhite
End If
End Sub

I only did the [12 Month] field if you want the other [X month] fields to do the same you will need to copy an paste the everything except the first and last lines of code and then replace [12 month] with the other field names.

Good Luck.