Maximum character count in crosstab column headings? (1 Viewer)

[Martynwheeler]

Hi,

I have successfully generated a crosstab query to generate a report. The headings are rather long (up to 186 characters), the headings I am seeing in the query are truncated. Is there a limit? If so, is there any way around it?

Thanks

Martyn

 

[plog]

I think 65 characters is the limit for field names in an Access query. So when you turn field values into field names that's the most that get displayed.

My advice would be to uncrosstab the query (make it just a summation query), export it to Excel and then make a pivot table off of the data and make that Excel your report. That will get you to 255 characters for field names.

 

[Martynwheeler]

hmm, that is an annoyance. Ironically I am trying to bring a spreadsheet that (sort of) did what I wanted into my database so I don't want to have to export it back to excel.

If I made the column headings the id number from the table that generates the full column titles could I use a lookup in the report that is generated?

Thanks

 

[Martynwheeler]

Okay, so I solved it like this:

assign id numbers to the col headers in the crosstab query

Then on the report based on the query do the following on load:

Private Sub Report_Open(Cancel As Integer)
    Dim c As Access.Control
    For Each c In Me.PageHeaderSection.Controls
        If IsNumeric(c.Caption) Then
            c.Caption = DLookup("[title]", "Skill", "[skill_id] = " & c.Caption)
        End If
    Next c
End Sub

This replaces the id with the title from the table...!