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
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