Dynamic Crosstab Report works in 2003 not in 2007 (1 Viewer)

[asya]

Hello everyone

I have an issue with my database. The database works fine in 2003. However when I try run it in 2007, one of the reports based on a cross-tab query (that works fine in 2007) would not open and comes up with a 3070 error ("Microsoft Access Database engine does not recognize " as a valid field name or expression"). It does not specify a field but only ".

The interesting thing is that there are other two dynamic crosstab reports that do work just fine. I'm puzzled.

Query Code:

PARAMETERS [Forms]![EmployeeSalesDialogBoxProjectID]![BeginningDate] DateTime, [Forms]![EmployeeSalesDialogBoxProjectID]![EndingDate] DateTime;
TRANSFORM Sum([REPORT BY EMPLOYEE 3MTH OUT by Project].SumOfWorkload) AS SumOfSumOfWorkload
SELECT [REPORT BY EMPLOYEE 3MTH OUT by Project].Type AS Manager, [REPORT BY EMPLOYEE 3MTH OUT by Project].[Employee name], [ProjectID] & " " & [Reference Name] AS Project
FROM [REPORT BY EMPLOYEE 3MTH OUT by Project] LEFT JOIN AccessImport ON [REPORT BY EMPLOYEE 3MTH OUT by Project].ProjectID = AccessImport.[Project #]
WHERE ((([REPORT BY EMPLOYEE 3MTH OUT by Project].Week) Between [Forms]![EmployeeSalesDialogBoxProjectID]![BeginningDate] And [Forms]![EmployeeSalesDialogBoxProjectID]![EndingDate]))
GROUP BY [REPORT BY EMPLOYEE 3MTH OUT by Project].Type, [REPORT BY EMPLOYEE 3MTH OUT by Project].[Employee name], [ProjectID] & " " & [Reference Name]
ORDER BY [REPORT BY EMPLOYEE 3MTH OUT by Project].Type, [REPORT BY EMPLOYEE 3MTH OUT by Project].[Employee name], [ProjectID] & " " & [Reference Name]
PIVOT [REPORT BY EMPLOYEE 3MTH OUT by Project].Week;

the vba fo cross tab:

' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 13
' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset
' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long
Private Sub InitVars()
 
Dim intX As Integer
' Initialize lngReportTotal variable.
lngReportTotal = 0
 
' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX
End Sub
 
Private Function xtabCnulls(varX As Variant)
 
' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If
End Function
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.
 
Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX
 
' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
End If
 
End Sub
 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
 
Dim intX As Integer
Dim lngRowTotal As Long
' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0
 
For intX = 4 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
Next intX
 
End If
End Sub
 
Private Sub Detail_Retreat()
' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious
End Sub
 
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
 
Dim intX As Integer
 
' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name
Next intX
 
' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX
End Sub
 
Private Sub Report_Close()
 
On Error Resume Next
' Close recordset.
rstReport.Close
 
End Sub
 
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
rstReport.Close
Cancel = True
End Sub
Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.
 
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
' Don't open report if EmployeeSalesDialogBox form isn't loaded.
If Not (IsLoaded("EmployeeSalesDialogBoxProjectID")) Then
Cancel = True
MsgBox "To preview or print this report, you must open " _
& "Date Period Dialog in Form view.", vbExclamation, _
"Must Open Dialog Box"
Exit Sub
End If
 
' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!EmployeeSalesDialogBoxProjectID
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("EmployeeSalesProjectID")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!EmployeeSalesDialogBoxProjectID!BeginningDate") _
= frm!BeginningDate
qdf.Parameters("Forms!EmployeeSalesDialogBoxProjectID!EndingDate") _
= frm!EndingDate
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
 
' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
 
End Sub
 
 
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst
'Initialize variables.
InitVars
End Sub

Any help will be appeciated!
Here's the cross tab query code: