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

Martynwheeler

Member
Local time
Today, 04:20
Joined
Jan 19, 2024
Messages
82
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

Banishment Pending
Local time
Yesterday, 22:20
Joined
May 11, 2011
Messages
11,675
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

Member
Local time
Today, 04:20
Joined
Jan 19, 2024
Messages
82
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

Member
Local time
Today, 04:20
Joined
Jan 19, 2024
Messages
82
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:

Code:
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...!
 

Users who are viewing this thread

Top Bottom