Access 2007 Crosstab query with parameters

[lisacrowe]

I have a simple database recording complaints. A crosstab query is based on a query which returns resolved complaints only. The crosstab has the field Complaint Type as a row heading and Outcome as a column heading.

I want to be able to select complaints which were resolved in a particular date range (field is Date resolved). This field is in the original query but not the crosstab.

How do I do it?

[Delerna]

In the query that the crosstab query is based off.

In the criteria for the field [Date Resolved] put the following
[code=sql]
[Date Resolved]>=[Please Enter From Date] and [Date Resolved]<=[Please Enter To Date]
[/code]

the criteria is referencing the fields [Please Enter From Date] and [Please Enter To Date]. Since these 2 fields don't exist in your query, (I hope), an input box will popup for each of them asking you to input their value.

PS
You can use the BETWEEN function instead if you want.

[lisacrowe]

Thank you for replying. I tried that first off but when I try to run the crosstab query I get an error message:

"The Microsoft Office Access database engine does not recognize '[startdate]' as a valid field or expression."

[lisacrowe]

Got it!

SQL is:

PARAMETERS [start] DateTime, [end] DateTime;
TRANSFORM Count(qResolved.ComplaintNumber) AS CountOfComplaintNumber
SELECT qResolved.[Complaint type], Count(qResolved.ComplaintNumber) AS [Total Of ComplaintNumber]
FROM qResolved
WHERE (qResolved.[Date Resolved] BETWEEN [start] AND [end])
GROUP BY qResolved.[Complaint type]
PIVOT qResolved.Outcome;

[KingKen]

Same issue but in Access 2003

I am having hell with my Cross tab query. I am trying to arrive at a table that displays a summary of job status by staff over a given period.

Here is the SQL I am using
TRANSFORM Count(SupportLo gs.Status) AS CountOfStatus
SELECT SupportLogs.Sup portStaff
FROM SupportLogs
WHERE (((SupportLogs. DateReported) Between [Forms]![frmSearch]![BegDate] And [Forms]![frmSearch]![EndDate]))
GROUP BY SupportLogs.Sup portStaff
PIVOT SupportLogs.Sta tus;

When I run this it gives me an error stating that "[Forms]![frmSearch]![BegDate]" is not a valid field name or expression.

I get the same thing if i try to use parameters and have it prompt for the date when it is executed.
Help Please!

[Delerna]

I havent used a pivot query in access that looks quite like yours so this is only a guess.
I am assuming that you are using text boxes for the dates
Try putting a .Text on the end of those controls.

[Forms]![frmSearch]![EndDate].Text

This statement

I get the same thing if i try to use parameters and have it prompt for the date when it is executed.

leads me to believe it won't work, but worth a try

[Don Wise]

I don't know if anyone answered this, but I found the answer today in doing the following:

Click on Parameters in your Query Design and enter [Forms]![NameofForm]![BeginDate] and select Date/Time for format. Do this for the begin date and again separately for end date on separate lines and close. Then run your query.