Changing Month Crosstab issue

[Noah114]

I currently have generated a report built off of a crosstab query with month as the column header. The issue I am having is that the months change. How can I build a query or report where if the months change from JAN FEB MAR to APR MAY JUN, it won't produce an error.

 

[Remou]

How about calling the months 1, 2, 3 in the report and the query, then change the labels programmatically?

 

[Noah114]

That sounds great, how can I change the labels programmatically?

 

[Remou]

You could either use a form and set the labels from that, or you could use a public variable. For example:

To open the report

Option Explicit
Public gstrMonths

Sub OpenReport()
gstrMonths = "Jan,Feb,Mar"
DoCmd.OpenReport "rptMonths", acViewPreview
End Sub

On the report

Private Sub Report_Open(Cancel As Integer)
astrMonths = Split(gstrMonths, ",")
For i = 1 To UBound(gstrMonths) + 1

Me("lbl" & i).Caption = gstrMonths(i - 1)
Next
End Sub

However, a form might be easier:

[tt]Me.lbl1.Caption = Forms!frmMonth!txtLabel1[/tt]

 

[dhookom]

Check out faq703-5466.

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]