Can not filter to report on a crosstab query source 1

[jckokko]

I get the error message "The Microsoft Jet database engine does not recognize '[forms]![ExpReport]![StartDate]' as a valid field name or expression."
The filter expression is
[Date] <= CDate([Forms]![ExpReport]![StartDate])

I found a thread with same problem, but it was posted on a &quot;Forms&quot; thread list.

Here's my SQL statement.
TRANSFORM Sum(tblExpense.Amnt) AS AmntOfSum
SELECT tblExpense.ExpCategId, tblTransaction.Date, Sum(tblExpense.Amnt) AS TotalSum
FROM (tblExpense INNER JOIN tblDepartment ON tblExpense.DepId = tblDepartment.DepId) INNER JOIN tblTransaction ON tblExpense.TransId = tblTransaction.TransId
GROUP BY tblExpense.ExpCategId, tblExpense.DepId, tblTransaction.Date
PIVOT tblDepartment.Name;

 

[dhookom]

YOu must specify the datatype of your criteria in all crosstabs. Select Query|Parameters and enter:
[Forms]![ExpReport]![StartDate] DateTime

BTW: Date is not a good name for a field since it is also the name of a function. Name is even a worse name for a field since every object has a name property. I would recommendt TransDate and DeptName.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jckokko]

Adding the Query|Parameters to the crosstab query resolved my problem, but it caused another problem: every time I open the report design, it prompts for the StartDate and EndDate. When I click on the print preview, it prompts me twice for each field.

 

[dhookom]

Are you closing the form at some point when you open the report?
Do you have subreports based on the same parameters?
If you can't figure this out, please paste your sql view into a reply.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jckokko]

Interesting.
When I work on the report design while its associated form design is opened, I do not get prompts, but I do otherwise.

Thanks.

 

[dhookom]

If the form isn't open, then you will be prompted since Access can't find the form and controls.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.