formatting report using crosstab query

[sumay03]

Report based on crosstab query, month as column heading, values as Row
heading along with contractID.
What I wish to achieve is to format the value where a [received] = -1
black, or [received] = 0 Red. on the same row.

JAN FEB MAR APR
CONTRACTID £100 £200 £300

currently have the following report

JAN FEB MAR APR
CONTRACTID £100 £200 Received (tick box)
£300 Not
received

any help appreciated

 

[Howard]

Report based on crosstab query, month as column heading, values as Row
heading along with contractID.
What I wish to achieve is to format the value where a [received] = -1
black, or [received] = 0 Red. on the same row.

JAN FEB MAR APR
CONTRACTID £100 £200 £300

currently have the following report

JAN FEB MAR APR
CONTRACTID £100 £200 Received (tick box)
£300 Not
received

any help appreciated

You need to write code in the DetailFormat section of the report that
runs each time one line of your detail is formatted. Something like this
should do it.

This assumes that you have called the text box that displays the
'received' field receivedBoxName. ie you refer to the text box name, not
the data field name. The 'Me' bit says that the textbox is part of the
detail section.
To get to where you write the code go to tools/macro/visual basic
editor. Then choose the report name from the list on the left

In the code page that opens, choose 'detail' from the left hand combo
box and format from the right one.



Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
if Me("receivedBoxName") = -1 then
Me(("receivedBoxName").BackColor = vbBlack
else
Me(("receivedBoxName").BackColor = vbRed
endif
end sub


Howard

 

[Duane Hookom]

Have you tried conditional formatting?
You stated "format the value" what value?

 

[Howard]

Have you tried conditional formatting?
You stated "format the value" what value?

Duane is right, conditional formatting is a much more obvious way to go
than my earlier solution. Don't know what I was thinking about!
Howard