Conditional Formatting on a report

[tarena]

I have a report with 4 fields that if the % in the fields falls below 80% then I want the field or the front to become a different color than the default color. Do I do the formatting in the report or in the query? How do I do this? My report name is "rptEffeciency", it pulls from "qryEffeciency" and the field names are "First", "Second" and "Third".

Thank you

 

[Remou]

With your report in design view, click on a textbox and choose Conditional Formatting from the Format menu. You can add a format to each textbox.

 

[tarena]

Thank you Remou...I did forget to mention that I was using Access 97 which does not have that option that I can find.

 

[Remou]

It is very important to mention that you are using 97 in your posts, it is quite different from all subsequent versions.

These may help:

http://support.microsoft.com/kb/302705/en-us

http://www.mvps.org/access/forms/frm0024.htm

 

[tarena]

yes...I do realize that and thought that I had mentioned it, I do realize my mistake.

Thanks very much.

 

[tarena]

There is no "OnFormat" event to select

 

[Remou]

Double-click on the grey detail bar. You should find a Format event on the property sheet.

 

[tarena]

Alright, I did what kb302705 told me to do and I put my code below. When I ran it I got...Run-time error '62506': Microsoft Access can't find the field 'Datafield' referred to in your expression.

My code is as follows; Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me!First > 80 Then
Me!First.BackColor = 255 'sets text box background to red
Else
Me!First.BackColor = 16777215 'sets text box background to white
End If

End Sub

"First" being the field in the report that holds the data that I want to cause the color to change from. If I debug it highlights the "If Me!First > 80 Then

 

[Remou]

I would say that first is a reserved word in 97 as well as 2000. Try bracketing:

If Me![First] > 80 Then

 

[tarena]

Alright, now I get the "Run-time error '62506': This action will reset the current code in break mode." and it still highlights the same line when I debug. See my code below;

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

If Me![First] > 80 Then
Me!First.BackColor = 255 'sets text box background to red
Else
Me!First.BackColor = 16777215 'sets text box background to white
End If

End Sub

 

[Remou]

You need to add brackets to all firsts:

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

If Me![First] > 80 Then
   Me![First].BackColor = 255 'sets text box background to red
Else
   Me![First].BackColor = 16777215 'sets text box background to white
End If

End Sub

Save the report and see if it will run.

 

[tarena]

Alright...did that and got the same Run-time error '62506': Reserved Error" and I hit debug and the same line is highlighted.

 

[Remou]

I have run out of ideas and can only suggest that there may be a data type mismatch. You could test this by using, say,

[tt]If Val(Me![First]) > 80 Then[/tt]

However, I do not have a copy of 97.

 

[tarena]

I will try that. Thank you for your help anyways.

 

[LarrySteele]

tarena,

In addition to First being a reserved word, I suspect that your textbox name is the same as the name of the underlying field. Access can get confused when it's not sure if First should be the recordset column or the report control.

If Remou's last suggestion does not work, then I recommend changing the name of your text box to txtFirst.

Then, the following *should* work:

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

    If Me.txtFirst.Value > 80 Then
       Me.txtFirst.BackColor = 255 'sets text box background to red
    Else
       Me.txtFirst.BackColor = 16777215 'sets text box background to white
    End If
End Sub

Like Remou, I do not have Access 97 available.

HTH,
Larry

 

[tarena]

Alright, I tried that one too...it ran once but with no changes to the back color and I closed everything out and reopened and it then gave me the Compile error: Method or data memeber not found. My code is...

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

If Me.txtFirst.Value > 80 Then
Me.txtFirst.BackColor = 255 'sets text box background to red
Else
Me.txtFirst.BackColor = 16777215 'sets text box background to white
End If
End Sub

the whole Private Sub line is highlighted in yellow and the If Me.txtFirst.Value > 80 Then is highlighted in blue.

 

[kdart301]

Tarena,

If you intend to have any one of the three fields below 80 change to red...make sure of the formatting of the fields, if is set to a format percent and the values are entered as .80 instead of 80.


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

If Me.First.Value < 0.8 Then
Me.First.BackColor = 255 'sets text box background to red
Else
Me.First.BackColor = 16777215 'sets text box background to white
End If
If Me.Second.Value < 0.8 Then
Me.Second.BackColor = 255 'sets text box background to red
Else
Me.Second.BackColor = 16777215 'sets text box background to white
End If
If Me.Third.Value < 0.8 Then
Me.Third.BackColor = 255 'sets text box background to red
Else
Me.Third.BackColor = 16777215 'sets text box background to white
End If
End Sub


I created the a sample database and this worked fine....Thanks.

Ken

 

[tarena]

genius Ken. Worked like a charm. Thank you so much and thank you also to Remou for all your help as well.