Problematic Crosstab Query (2000 SR1)

Topic

New Reply

We are trying to use a crosstab query to drive a report, and we are trying to use a control on a form to supply a date as a part of the criteria. It fails saying that the form reference is an invalid object. The SQL looks like this

TRANSFORM Sum(qry_WeeklyStatistics.EventCount) AS SumOfEventCount
SELECT qry_WeeklyStatistics.UserName
FROM qry_WeeklyStatistics
WHERE (((qry_WeeklyStatistics.Event_Date) Between DateAdd(“d”,-6,[Forms]![frm_MyForm]![txt_WeekEnd_dt]) And [Forms]![frm_MyForm]![txt_WeekEnd_dt]))
GROUP BY qry_WeeklyStatistics.UserName
PIVOT qry_WeeklyStatistics.event_Desc;

If we simply replace the Forms![frm_MyForm]![txt_WeekEnd_dt] with a valid date, it works fine. By the same token, if we put the expression into the source query (qry_WeeklyStatistics) as a criteria, that query will run just fine, but the crosstab query still fails. Can someone shed some light on what’s going on here? Thanks in advance.

Reply | Quote

Replies

Wendell,

When I’ve tried to run a similar report (based on a crosstab query, which was based on another query), I had to use the Query . . . Parameters dialog box to get the query to recognize the Text box in the selection form. Used the [Forms]![frmFormName]![txtTextBoxName] name in the Parameter column, and Date/Time in the Data Type column.

I used the Query . . . Parameters box on the first query, not on the crosstab query itself.

HTH,

Tom

Reply | Quote

Thanks – that worked like a charm – guess I’m running a little low on sleep and sense here. I thought about trying to put a parameter in, but decided it wouldn’t work for some strange reason. Thanks for similar responses from RichUK, Francois and Charlotte.

Reply | Quote

For reference it is in this KB Article:
Q209778

Reply | Quote

Define the Parameters in the crosstab as Date/Time

Reply | Quote

Maybe not elegant but it works.
Create a function:

Function Mydate() As Date
Mydate = [Forms]![frm_MyForm]![txt_WeekEnd_dt]
End Function

Change the query to
TRANSFORM Sum(qry_WeeklyStatistics.EventCount) AS SumOfEventCount
SELECT qry_WeeklyStatistics.UserName
FROM qry_WeeklyStatistics
WHERE (((qry_WeeklyStatistics.Event_Date) Between DateAdd(“d”,-6,Mydate()) And Mydate()))
GROUP BY qry_WeeklyStatistics.UserName
PIVOT qry_WeeklyStatistics.event_Desc;

Reply | Quote

Put the full form reference for txt_WeekEnd_dt into the parameters of the crosstab query. I’m running reports based on a similar situation right now, and it even works in Access 97! The only catch to this is that if you want to open a recordset in code based on the query so that you can tell if there are records for the report, you’ll have to use a querydef object and pass the parameters in as values, because opened in code, it won’t be able to find the form values for itself.

Reply | Quote