Change values in group headr based on Group value 1

[tuckerssb]

This is an Access 2000 ADP & sql DB, report is being run by a docmd.openreport in vb6.

I have a report which has column headings in a group header. I want to change these column headings based on the value of the group.

Lets assume I have a group named "state" whose values can be "A" or "B". While the group value is "A", I want the column heading to be "Received", else "Delivered".

I put a textbox in the group header with the following as a control source:

=if([state]="A", "Received", "Delivered")

The report fails with the Visual Basic error:

"Run-time error:

There was a problem accessing a property or method of the OLE object."

Anybody got a way of achieving my goal??

tom

 

[dhookom]

How about implementing this in your record source:
[tt][blue]
Case When State='A' Then 'Received' Else 'Delivered' End as RDStatus[/blue][/tt]

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[tuckerssb]

I understand the concept of your suggestion and am a little confused about the implementation.

I have tried using your phrase "Case When State='A' Then 'Received' Else 'Delivered' End as RDStatus" but rx'd errors.... I'll pursue some more...

My original report need required presenting data separately from 3 different queries in a single report. I was not familiar with the subreport feature so in the VB code of the application I populated a temp table in sql with the needed data. In the detail of the report there are 4 columns of data. The heading or title for each column needs to change based on the value of a column which is grouped.

Group Column "Status" = "A" or "B"
if "A" then field1 heading = "Ship Type" else "Release#"
and so on for 3 fields/columns.

I will continue to try your suggestion, and I will also give subreports a go...

thx for the response, your replies are often seen here so I am glad I got a "guru" to respond....

 

[tuckerssb]

I now have it working with your suggestion. Instead of using the table from sql as a recors source I built a stored procedure referencing the table an included 3 instance of your suggested case statement.

At first there was a problem, I used your statement exactly and it was syntactically incorrect.

Case When State='A' Then 'Received' Else 'Delivered' End as RDStatus

should be

Case State When "A" Then 'Received' Else 'Delivered' ......

thx for help, it was your coneptual idea that got me a solution.