Crosstab Report with Dynamic Fields

S

scott

I am trying to create a crosstab report with dynamic fields. I am using the
script published by Duane Hookum (1/22/00) that creates alias columns, etc.
The report runs great and will produce exactly what is needed, unless there
is no data for an alias column. In that case, it returns the message that
the Mosrosfot Jet Database engine does not recognize "B" as a valid field.

Of course, I had to modify the script to accomodate the data I am trying to
report, so it must have something to do wht the way I modified it. I am
using a static number of columns (10) in the report (whereas the Hookum
script allows you to select the number of columns to print), so I made an
unbound field named lboNumColumns in the form that launches the script that
just has the number 10 in it. After that, I think the script is pretty close
to what is in the Hookum script.

Below is the script in my function. Let me know if you need to know
something about the data or the report to help me.

Thxs


Function UpdateReportAlias(pbytNumColumns As Byte) As Long
'============================================================
' Purpose:
' Copyright: 1999 Business Results
' Company: Business Results
' Phone: 715-835-8130
' E-Mail: (e-mail address removed)
' Programmer: Duane Hookom
' Called From:
' Date: 1/22/00
' Parameters:
'============================================================
On Error GoTo UpdateReportAlias_Err
Dim strErrMsg As String 'For Error Handling

Dim strSQL As String
Dim intAlias As Integer
Dim bytLevel As Byte
Dim lngProjID As Long
Dim bytMaxColumns As Byte

Dim db As Database
Dim rs As Recordset

strSQL = "Delete * from ReportAlias"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.OpenQuery "qryReportAlias" 'create table of reports matching criteria
DoCmd.SetWarnings True

bytMaxColumns = pbytNumColumns

Set db = CurrentDb
Set rs = db.OpenRecordset("ReportAlias") 'table used to redefine/alias
the column headings
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngProjID = !ProjID
bytLevel = 0
intAlias = 65 'ascii value of 'A'
Do While !ProjID = lngProjID
.Edit
!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

UpdateReportAlias_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

UpdateReportAlias_Err:
Select Case Err
Case Else
UpdateReportAlias = Err.Number
Resume UpdateReportAlias_Exit
End Select

End Function
 
G

George Nicholson

Did you set Column Headings in the query to reflect your static 10 field
limit?
Column Headings = 'A','B','C',etc.
That forces the query to always generate 10 columns (even if they are empty)
and should prevent "..can't find field 'B'..." messages since the query will
always include B.
 
S

scott

Bingo. Thanks a lot.

George Nicholson said:
Did you set Column Headings in the query to reflect your static 10 field
limit?
Column Headings = 'A','B','C',etc.
That forces the query to always generate 10 columns (even if they are empty)
and should prevent "..can't find field 'B'..." messages since the query will
always include B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top