crosstab query help needed 1

[hhhranch]

I am new to Access (we use version 97). I have a crosstab query which gives me type of case (row heading) and opener (column heading). It gives me the count of those marked completed for each record in the table. What I'd like to do is narrow that to specify a date range (between..and..) I tried entering it as criteria in the query but I get a message that Microsoft Jet database engine does not recognize 'Enter begin...]' as a valid field name or expression. I don't know beyond the basics so I'm unsure where to go from here. Thanks for any assistance.

 

[traingamer]

[Enter begin date] - parameters need a bracket on each end of the parameter.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

In a CrossTab query you have to define the parameters:
menu Query -> Parameters ...
And be sure to use [Enter begin date]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hhhranch]

Thank you for your replies. I want to say I mistyped when I posted. I did have the beginning bracket when I did the query. I was in a hurry when I posted the message and accidentally left it out. You guys are great in catching that. The actual error message was Microsoft Jet database engine does not recognize '[Enter begin...]' as a valid field name or expression. All assistance is greatly appreciated. Thanks again.

 

[PHV]

Can you please post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hhhranch]

I hope I'm doing this right. I am a novice. The original crosstab query was great except it gave me all cases completed.

TRANSFORM Count(Intake2.Comp) AS CountOfComp
SELECT Intake2.[T/N/D], Count(Intake2.Comp) AS [Total Completed]
FROM Intake2
GROUP BY Intake2.[T/N/D]
PIVOT Intake2.Opr;

Anyway, I think I found something that will work for me. I took the above crosstab and changed it to a select query and then added the criteria. It ran without error. The code for it is:

SELECT Intake2.[T/N/D], Intake2.Opr, Count(Intake2.Comp) AS CountOfComp, Count(Intake2.Comp) AS [Total Completed]
FROM Intake2
GROUP BY Intake2.[T/N/D], Intake2.Opr, Intake2.Comp
HAVING (((Intake2.Comp) Between [Enter Begin Date] And [Enter End Date]));

Thanks

 

[PHV]

And this ?
PARAMETERS [Enter Begin Date] DateTime, [Enter End Date] DateTime;
TRANSFORM Count(Intake2.Comp) AS CountOfComp
SELECT Intake2.[T/N/D], Count(Intake2.Comp) AS [Total Completed]
FROM Intake2
WHERE Intake2.Comp Between [Enter Begin Date] And [Enter End Date]
GROUP BY Intake2.[T/N/D]
PIVOT Intake2.Opr;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hhhranch]

WOW, That's perfect. THANKS!!!