Conditional formatting problem

[ridders]

Hi

I have a report based on a student grades crosstab query with a number of
different cases:
a) Point scores
b) Residual point scores
c) Grades
d) Grades (with Residual scores)
I have successfully used code to apply conditional formatting where the
field values are numbers i.e. a) & b) above
e.g.Set fc = .Add(acFieldValue, acLessThan, 34)
fc.ForeColor = vbBlack
fc.BackColor = vbRed
fc.FontBold = True

I can't get this to work where using grades
e.g. Set fc = .Add(acFieldValue, acLessThan, "C")
fc.ForeColor = vbBlack
fc.BackColor = vbGreen
fc.FontBold = True
I get a enter parameter value box asking me to define "C"
Surely this should be easy. How do I solve this?

Also I want to do something more complex still - see d) above
I want to display grades but format based on residuals!

I have tried displaying both as the crosstab query values
e.g. C -6 where a space is used as a separator
I would then want to display the grade C but use format ting for -6!
Is it possible to do this?

I've tried formatting like this:
Dim FieldValue As Long
FieldValue = Mid(acFieldValue, InStr(1, acFieldValue, " ") +
1)
Debug.Print FieldValue
Set fc = .Add(FieldValue, acLessThan, -6)
fc.ForeColor = vbBlack
fc.BackColor = vbRed
fc.FontBold = True

This doesn't work as the FieldValue always equals 0 for some reason

I also can't see how to modify the cell value after applying formatting

Is it possible to do this at all?

 

[Marshall Barton]

I have a report based on a student grades crosstab query with a number of
different cases:
a) Point scores
b) Residual point scores
c) Grades
d) Grades (with Residual scores)
I have successfully used code to apply conditional formatting where the
field values are numbers i.e. a) & b) above
e.g.Set fc = .Add(acFieldValue, acLessThan, 34)
fc.ForeColor = vbBlack
fc.BackColor = vbRed
fc.FontBold = True

I can't get this to work where using grades
e.g. Set fc = .Add(acFieldValue, acLessThan, "C")
fc.ForeColor = vbBlack
fc.BackColor = vbGreen
fc.FontBold = True
I get a enter parameter value box asking me to define "C"
Surely this should be easy. How do I solve this?

Also I want to do something more complex still - see d) above
I want to display grades but format based on residuals!

I have tried displaying both as the crosstab query values
e.g. C -6 where a space is used as a separator
I would then want to display the grade C but use format ting for -6!
Is it possible to do this?

I've tried formatting like this:
Dim FieldValue As Long
FieldValue = Mid(acFieldValue, InStr(1, acFieldValue, " ") +
1)
Debug.Print FieldValue
Set fc = .Add(FieldValue, acLessThan, -6)
fc.ForeColor = vbBlack
fc.BackColor = vbRed
fc.FontBold = True

This doesn't work as the FieldValue always equals 0 for some reason

I also can't see how to modify the cell value after applying formatting

Is it possible to do this at all?

I have no idea what all that means or if any of it is
possible or not. I think I can say that what ever it is you
want, it would be a heck of a lot easier to do and easier to
understand if you avoided CF and just used code to set the
control properties in the section's Format event.

Here's some sample air code that may or may not be relevant
to your specific question:

With Me.[Point scores]
If .Value < 34 Then
.ForeColor = vbBlack
.BackColor = vbRed
.FontBold = True
Else
.ForeColor = vbBlue
.BackColor = vbYellow
.FontBold = False
End If
End With

With Me.Grades
If .Value < "C" Then
.ForeColor = vbBlack
.BackColor = vbGreen
.FontBold = True
Else
.ForeColor = vbBlue
.BackColor = vbWhite
.FontBold = False
End If
End With
. . .

 

[ridders]

Hi Marshall

I've solved the conditional formatting issue for the grades case.
It works if I use single quotes inside the double quotes e.g. "'C'"

The other situation is much more difficult.
Thanks for your reply but I I don't think I can use the method you suggest.

My formatting is applied using the report open event rather than detail
format event. However I don't think that using detail format event is the
issue here

The report itself is very complex and is based on a number of crosstab
queries.
The values that are generated in this report are placed in textboxes
Text1...Text2 etc where the field values are the students' grades (or points
or something else called residuals) in each subject. The values generated
depend on selections made by the user.
The number of textboxes can also vary (up to 30) depending on the number of
subjects studied in that year group.

If all that makes little sense, the main point is that all the values are
stored in controls called Text1...Text30. All works fine except for the case
where I am showing grades but want the formatting to be based on another set
of values called residuals.

My question is this:
Is it possible to apply conditional formatting where the conditions are not
based on the values shown in the textboxes but instead on another expression/

Does that make any sense at all?

I have a report based on a student grades crosstab query with a number of
different cases:
a) Point scores
b) Residual point scores
c) Grades
d) Grades (with Residual scores)
I have successfully used code to apply conditional formatting where the
field values are numbers i.e. a) & b) above
e.g.Set fc = .Add(acFieldValue, acLessThan, 34)
fc.ForeColor = vbBlack
fc.BackColor = vbRed
fc.FontBold = True

I can't get this to work where using grades
e.g. Set fc = .Add(acFieldValue, acLessThan, "C")
fc.ForeColor = vbBlack
fc.BackColor = vbGreen
fc.FontBold = True
I get a enter parameter value box asking me to define "C"
Surely this should be easy. How do I solve this?

Also I want to do something more complex still - see d) above
I want to display grades but format based on residuals!

I have tried displaying both as the crosstab query values
e.g. C -6 where a space is used as a separator
I would then want to display the grade C but use format ting for -6!
Is it possible to do this?

I've tried formatting like this:
Dim FieldValue As Long
FieldValue = Mid(acFieldValue, InStr(1, acFieldValue, " ") +
1)
Debug.Print FieldValue
Set fc = .Add(FieldValue, acLessThan, -6)
fc.ForeColor = vbBlack
fc.BackColor = vbRed
fc.FontBold = True

This doesn't work as the FieldValue always equals 0 for some reason

I also can't see how to modify the cell value after applying formatting

Is it possible to do this at all?

I have no idea what all that means or if any of it is
possible or not. I think I can say that what ever it is you
want, it would be a heck of a lot easier to do and easier to
understand if you avoided CF and just used code to set the
control properties in the section's Format event.

Here's some sample air code that may or may not be relevant
to your specific question:

With Me.[Point scores]
If .Value < 34 Then
.ForeColor = vbBlack
.BackColor = vbRed
.FontBold = True
Else
.ForeColor = vbBlue
.BackColor = vbYellow
.FontBold = False
End If
End With

With Me.Grades
If .Value < "C" Then
.ForeColor = vbBlack
.BackColor = vbGreen
.FontBold = True
Else
.ForeColor = vbBlue
.BackColor = vbWhite
.FontBold = False
End If
End With
. . .

 

[Marshall Barton]

I've solved the conditional formatting issue for the grades case.
It works if I use single quotes inside the double quotes e.g. "'C'"

The other situation is much more difficult.
Thanks for your reply but I I don't think I can use the method you suggest.

My formatting is applied using the report open event rather than detail
format event. However I don't think that using detail format event is the
issue here

The report itself is very complex and is based on a number of crosstab
queries.
The values that are generated in this report are placed in textboxes
Text1...Text2 etc where the field values are the students' grades (or points
or something else called residuals) in each subject. The values generated
depend on selections made by the user.
The number of textboxes can also vary (up to 30) depending on the number of
subjects studied in that year group.

If all that makes little sense, the main point is that all the values are
stored in controls called Text1...Text30. All works fine except for the case
where I am showing grades but want the formatting to be based on another set
of values called residuals.

My question is this:
Is it possible to apply conditional formatting where the conditions are not
based on the values shown in the textboxes but instead on another expression/

Does that make any sense at all?

Well, it does make sense and I guess you can do it that way,
but I would still use the detail section's Format event.
Note that you can loop through a "group" of controls that
are named with a common prefix and a sequential numeric
suffix:

For k = 1 To 30
With Me("Text" & k)
If .Value < 34 Then
.ForeColor = vbBlack
.BackColor = vbRed
.FontBold = True
Else
.ForeColor = vbBlue
.BackColor = vbYellow
.FontBold = False
End If
End With
Next k

But, you should be able to do it your way by using something
like:

Set fc = .Add acExpression, , "[fieldx] < [fieldy]"