Automatically Formatting Text in a report

[striker]

I done a reasonable amount of research and I do't believe I can do what I want within the report.


I have a task outstanding report and want to format the text differently depending how far out of date the task is.

ie under 30 days late in bold
between 30 and 60 days late in bold italics
over 60 days old in flashing red 128 pitch font.

Is this possible and how.


TIA
Steve.

 

[IMO]

Are you using A2K or above? If so, in the reports design view, select the text field you want to format and goto Format >>> Conditional Formatting. From here you can set the conditions of the field.

IMO

 

[striker]

Fraid not good old 97.

 

[IMO]

You could try messing about with the reports detail, like....

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.YourTextField.Value < 30 Then
Me.YourTextField.ForeColor = vbBlue
End If

If Me.YourTextField.Value > 30 And Me.YourTextField < 60 Then
Me.YourTextField.ForeColor = 16744703
End If

If Me.YourTextField.Value > 60 Then
Me.YourTextField.ForeColor = vbRed
End If

End Sub

Hope this helps

IMO

 

[IMO]

I think this is a bit closer to what you want...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.YourTextField.Value < 30 Then
Me.YourTextField.FontBold = False
Me.YourTextField.FontItalic = False
Me.YourTextField.ForeColor = 0
End If

If Me.YourTextField.Value > 30 And Me.YourTextField < 60 Then
Me.YourTextField.FontBold = True
Me.YourTextField.FontItalic = True
Me.YourTextField.ForeColor = 0
End If

If Me.YourTextField.Value > 60 Then
Me.YourTextField.FontBold = True
Me.YourTextField.FontItalic = True
Me.YourTextField.ForeColor = vbRed
End If

End Sub

IMO

 

[striker]

IMO,


Thanks for the help works like a dream.

 

[IMO]

Glad it worked

IMO

 

[Cameroncha]

what about....

what if the field that i wanted to format was a date field... how would i modify the code you provided?

 

[IMO]

Much the same, something like...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.YourDateField.Value < #1/30/2001# Then
Me.YourDateField.FontBold = False
Me.YourDateField.FontItalic = False
Me.YourDateField.ForeColor = 0
End If

If Me.YourDateField.Value > #1/30/2001# And Me.YourDateField.< #1/30/2006# Then
Me.YourDateField.FontBold = True
Me.YourDateField.FontItalic = True
Me.YourDateField.ForeColor = 0
End If

If Me.YourDateField.Value > #1/30/2006# Then
Me.YourDateField.FontBold = True
Me.YourDateField.FontItalic = True
Me.YourDateField.ForeColor = vbRed
End If

End Sub

changing "YourDateField" to the name of the control and the dates accordingly.

IMO

 

[teiben]

Conditional ~ using current date

I'm using Access 97 so I can't use the conditional formating to build by report. I was wonder, how would I accomplish this:

I have a report and I want the most current record (using a date field to print) a different color, in other words how would I code it that I only need the most recent date?

 

[striker]

formatting

teiben,

I assume that you can't sort the report so that the newest date is the first record.

If you can't then you will have to sit down and think up some formula to work out the most recent date.

Give me some info on the size the report is likely to be ie how many records. and how big the table is etc and i'll try and come up with an answer.

 

[IMO]

This will return the latest date on the Report in Bold/Red...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.YourDateControl = DMax("YourTableColumn", "YourTable") Then
    Me.YourDateControl.FontBold = True
    Me.YourDateControl.ForeColor = vbRed
Else
    Me.YourDateControl.FontBold = False
    Me.YourDateControl.ForeColor = vbBlack
End If

End Sub

and in the Reports properties...

Filter On = Yes
Order By = [YourDateControl] Desc
Order By On = Yes

will list them in Descending Order.

IMO

 

[teiben]

I guess I'm brain dead, but what am I doing wrong?

The report is sorting Date Recd as desenting
The tablename is Bla
The Date_Recd is the field

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Date_Recd = DMax("[Date_Recd]", "Bla") Then
Me.Date_Recd.FontBold = True
Me.Date_Recd.ForeColor = vbRed
Else
Me.Date_Recd.FontBold = False
Me.Date_Recd.ForeColor = vbBlack
End If

End Sub

 

[IMO]

The Date_Recd is the field

Is the column name in the table Bla "Date_Recd"? If not, you need to put the column name from Table Bla in the DMax code line, not the field name from the report.

If Me.Date_Recd = DMax("[Table Bla Column Name Here]", "Bla") Then

IMO

 

[teiben]

I figured it out, if it's based on a table it works, but if I base it on a parameter query, it doesn't

 

[teiben]

What do you mean by column name?