Dynamic crosstab report sorting headings & dlookup
Windows XP 
Access 2010 32bit
Hi Guys,
I am stuck with a tricky problem.
I am currently working on a report that will show monthly trade position. I have to use the crosstab query view. I successfully implemented the already existing example of dynamic crosstab report (Hail to the man that wrote the code!).
The problem I have been stuck on the last few days is how to automate column order based on months. The column headers are not in date format and there are couple of them that can't be converted to date format (like Inventory or Inventory in Transit). In order to tackle this difficulty I created a separate table with a month id key that gives number equivalent of the month. Now I want to sort the report dynamic headers according to this order.
Dlookup function in the query didnt work for me. I am trying to use VBA dlookup, but when I select the criteria I get first row entry in all headers.
Now I add couple of words on the workings:
Report 605 delta is feed by qryCrossTabReport2, which takes data from 605DailyDelta crosstab query using a macro.
Column heading in the report are taken from 605DailyDelta query
SortOrder Table contains the months ID in the MonthID Field
605DailyDelta column headers are based on MonthID Field from a query 605 tdelta (which is a union query it has a relationship with SortOrder table)
I am trying to make work the dlookup, but if you have any other suggestion how to set the monthly order in headings I would gladly test them.
I tried query sorting without much succes.
I will appreciate any input.
Thanks !
Here's the code : ' Note when I run the report based on this i have the first row from my source in all the headers
And for dlookup I replace the =fld.Name (that works fine but does not sort column headers)
Option Compare Database
Option Explicit
Dim ReportLabel(22) As String
Private Sub Report_Open(Cancel As Integer)
Dim i As Integer
For i = 0 To 22
ReportLabel(i) = ""
Next i
Call CreateReportQuery
End Sub
Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim fld As Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim i As Integer
Dim MonthID As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("605DailyDelta")
indexx = 0
For Each fld In qdf.Fields
'If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & "[" & fld.Name & "] as Field" & indexx & ", "
ReportLabel(indexx) = DLookup("Trimmed", "SortOrder", "MonthID =" & MonthID) 'fld.Name
'End If
' MsgBox Label(indexx)
indexx = indexx + 1
Next fld
For i = indexx To 22
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)
strSQL = "Select " & FieldList & " From 605DailyDelta"
db.QueryDefs.Delete "qryCrossTabReport"
Set qdf = db.CreateQueryDef("qryCrossTabReport", strSQL)
'MsgBox strSQL
Exit_CreateQuery:
Exit Sub
Err_CreateQuery:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_CreateQuery
End If
End Sub
Function FillLabel(LabelNumber As Integer) As String
FillLabel = Nz(ReportLabel(LabelNumber), "")
End Function
Reply With Quote
) this is the best I could come up with. 
Originally Posted by June7
