Report field Colur dependent on Date 1

[Dilly]

I have a report that shows borrowed equipment with the date it is due to be returned. I would like to highlight the records that have to go within the next 30 days. I cannot seem to find the correct code to highlight ones that are due within 30 days or less of todays date.

Please note I am self teaching and finding VBA quite challenging.

 

[PantoKing]

Dilly,

If you're report has a text box called txtDateDue which contains the date that the item is due to be returned you can use the following code in the OnPrint event of the section detail:-


Dim daysdue As Integer
daysdue = Me![txtDatedue] - Now()
If daysdue > 30 Then
Me!txtDatedue.ForeColor = 255
Else
Me!txtDatedue.ForeColor = 0
End If

This will highlight the date field only of any items that are due within the next 30 days.

If you wish to include more simply add the text box names with .forecolor.

HTH

Steve.

 

[Garridon]

Another alternative is to use the Conditional Formatting function. In Design view, click on the field in question, select Format>Conditional Formatting. Then, for the criteria, insert something like:

<Date()-30

Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

http://www.david-hedison.com

 

[PantoKing]

Linda,

I've only ever seen conditional formatting in Excel, my version of Access 2000 hasn't got it anyway. Unless I'm missing something.

Cheers,

Steve.

 

[MichaelRed]

Well, even better is to set all of the controls in the section to have their backgrounds transparent and just manipulate the detail backcolor in a similar fashion. Thsi technique is at least as old as access ver 2.x and highlights the whole record in &quot;one fell swoop&quot;. It is also possible to individually color code records in a variety of categories in this manner - without much extra code.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over