More dynamic reporting questions.

[Kimbertha]

Greetings Everyone!

I have a crosstab query that displays years 2000 to 2010 across the top. I have a basic report that works well and is aesthetically pleasing, however I am struggling with two questions about the design.

Question 1
This year the crosstab query will display years 2000 to 2010. Next year it will display 2001 to 2011. Is there a way for the report to dynamically capture and display the change in years as the oldest year drops off and a new year is added? Otherwise I would have to manually update the field names in the report each year, which would be a bummer.

Question 2
Is there a way to perform rounding calculations within the detail fields? I am familiar with how to create an "unbound" box and use formulas there. But if I place a calculation directly in a detail report, I get a circular reference error, which makes sense I guess, because a field cannot calculate its own value. I am reluctant to replace all my detail fields with unbound fields only because I have done a lot of work formatting the detail fields, getting their placement just right, etc.

Any suggestions would be great. Thank you for reading.



Kim

[RuralGuy]

My guess to question 1 is yes but we need more minformation for a more specific answer. As for question 2: are you aware you can have invisible controls on your report for such operations?

[weekend00]

I don't know how to deal with Q1.

For Q2, can you just format the bounded textbox?

[ajetrumpet]

if I place a calculation directly in a detail report, I get a circular reference error

Kim,

I believe a circular reference error can also be caused by either the same control source for two controls or the same name for two controls. Dont' quote me though...Access usually stops you from exiting the 'name' field of a control if it's duplicated after typing it in...

[Kimbertha]

Update!

I fixed my second question (see below) by rewriting the report detail and totals section. It was painful because the computer I have at this client is s l o w . But that part is working.

Question 2
Is there a way to perform rounding calculations within the detail fields? I am familiar with how to create an "unbound" box and use formulas there. But if I place a calculation directly in a detail report, I get a circular reference error, which makes sense I guess, because a field cannot calculate its own value. I am reluctant to replace all my detail fields with unbound fields only because I have done a lot of work formatting the detail fields, getting their placement just right, etc.

But for my first question (see below), I am stumped. If there is not a direct solution, there must be a workaround . . .

Question 1
This year the crosstab query will display years 2000 to 2010. Next year it will display 2001 to 2011. Is there a way for the report to dynamically capture and display the change in years as the oldest year drops off and a new year is added? Otherwise I would have to manually update the field names in the report each year, which would be a bummer.

[weekend00]

if I were you, I won't put so much effort into such once a year thing. I would just modify it every year, better than thinking hard to get lot of code - easy to cause problem - to perform the changes.

If you still want to do it, following may help:

1 Name the textboxes in the report in order: tb1, tb2, tb3...
2 Name the label accordingly to: label1, label2, label3...
3 In the open event of the report, make up the record source statement
4 In the detail format event of the report, change the caption of each label.

Sample code as following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' modify captions
'QryFlex is the cross tab query name
   Dim rs As dao.Recordset
    Dim col As Long
    Set rs = CurrentDb.OpenRecordset("select * from QryFlex where false")
    For col = 0 To rs.Fields.Count - 1
       Me.Controls("Label" & col).Caption = rs.Fields(col).Name
    Next
    Set rs = Nothing
End Sub
Private Sub Report_Open(Cancel As Integer)
'change record source
    Dim rs As dao.Recordset
    Dim col As Long
    Dim source As String
    Set rs = CurrentDb.OpenRecordset("select * from QryFlex where false")
    source = "select "
    For col = 0 To rs.Fields.Count - 1
        source = source & "[" & rs.Fields(col).Name & "] as tb" & col & ","
    Next
    Set rs = Nothing
    source = Left(source, Len(source) - 1) & " from qryflex"
    Me.RecordSource = source
End Sub