Using 'Parameters' with Cross-tab Queries

[hoggbottom59]

Um, has to be said I do hate Cross-tab queries.

I'm trying to set-up a cross-tab query which accepts user input parameters. When I originally played with it I'm sure it accepted parameters.

The 'where' is set for a field called 'Opened' which contains dates. The query works if the criteria is set as 'between #01/05/01# and #31/01/02'.
If I insert parameters instead it doesn't -
'Between [First Date] And [Last Date]'

A message "The Microsoft Jet Database Engine does not recognize '[First Date]' as a valid field name or expression" appears.

Why is it being such a pain? I need more coffee.

Thanks,
Leon.

 

[hoggbottom59]

The SQL of this Cross-tab which brings up an error message is

TRANSFORM Count(tblNEWHelpdeskMain.LogNumber) AS [The Value]
SELECT tblNEWHelpdeskMain.CallType, Count(tblNEWHelpdeskMain.LogNumber) AS [Call Type Total]
FROM tblNEWHelpdeskMain
WHERE (((tblNEWHelpdeskMain.[Opened]) Between [Start] And [End]))
GROUP BY tblNEWHelpdeskMain.CallType
PIVOT Format([Opened],"yyyy mm"


I have a working example of a Cross-tab which accepts parameters (below). I can't see what is wrong.

TRANSFORM Count(HELPDESK.[CALL LOG NUMBER]) AS [The Value]
SELECT HELPDESK.[CALL TYPE], Count(HELPDESK.[CALL LOG NUMBER]) AS [Total Of CALL LOG NUMBER]
FROM HELPDESK
WHERE (((HELPDESK.[DATE OPENED]) Between [Start] And [End]))
GROUP BY HELPDESK.[CALL TYPE]
PIVOT Format([DATE OPENED],"mmm&quot In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

Cheers,
Leon.

 

[Brockers]

In the Design View, you simply have to define the Parameters Data Types. This is done from the Query Menu then choose parameters,

In Column 1 type the Text you are Using in the Parameter prompt for the user eg. Enter Start Date

In Column 2 choose the Data Type eg. Date/Time

Run the Query again!

 

[toekneel]

I never had looked for a parameter under menu items, and sure enough- there it is! But when I enter the parameter and the data type, run the query and enter in my parameters, it doesn't filter anything out. I tried rebuilding my query again just to be certain that I included my parameter field as one of the fields in the cross tab, and again it didn't filter anything out. What am I missing here?

 

[Brockers]

Make sure you still have your parameters in the Criteria row as well, then use the Parameters in the menu to define the Data Type, it then links to the Fields Criteria row by using the EXACT (Inc Spaces etc) same Text you had used in the initial parameter otherwise it has no logical way of knowing which field the Parameter refers to!

Example:

Field - StartDate
Criteria - >[Enter Start Date]
Parameter Defined in Menu Column 1 - Enter Start Date
Parameter Defined in Menu Column 2 - Date/Time


Regards

Brockers
Instructor
New Horizons Manchester England