Dynamic label captions

[harvesterlily]

I work a lot in Access using VBA and SQL. For whatever reason the reports sometimes throw me for a loop.

My current project is to automate our billing.

I am working in Access 2000. The reports are based a query. We bill based on income categories. I am grouping by categories. So far the layout and info is displaying properly. Instead of my category code, I would like to have a label with different captions based on the category value.

Example:
BillCat is name of field in my query results and the textbox on the report.

In the report detail code:

If BillCat = "A" Then
Me.lblA.visible = True
me.lblA.caption = "Cash Collections for March 2005"
ElseIf BillCat = "B" Then
Me.lblB.visible = True
me.lblB.caption = "Insurance Collections for March 2005"
elseif etc.....
end if

Many of our clients have multiple categories. My test client has 2 categories. This code is resulting in one label with the caption for lblA. It's not corresponding with the proper category. The other label is visible with "" appearing on the report.

Can someone please point me in the right direction on how to accomplish this?

Thanks

 

[harvesterlily]

Okay, I have place the code in the group header section - now both my labels have captions and they correspond to the proper category. The problem is now that the second category has 2 labels that overlap one another so that neither is legible. Needing only one label per category.

 

[harvesterlily]

figured it out:

in group header section

If billcat = "a" Then
me.lbla.visible =true
me.lbla.caption = "Cash Collections"
me.lblb.visible =false
me.lblc.visible = false
etc...
elseIf billcat = "b" then
me.lblb.visible =true
me.lblb.caption = "Ins collections"
me.lbla.visible =false
me.lblc.visible =false
elseif etc....
End if

 

[harvesterlily]

no really figured it out:

in group header section

If billcat = "a" Then
me.lblCat = "Cash Collections"
me.lblCat.visible
ElseIf billcat = "b" Then
me.lblCat = Insurance Collections"
me.lblCat.visible
elseif etc...
End if

instead of having multiple labels with different captions, just reused label and assigned captions.