Looking for help with crosstab queries and their relationship to forms

[MConlisk]

I am having a problem using a form to supply dates to a crosstab query. I have a form named "frmGetDates_ESS" and it has two text fields "dfrom" and "dto". Here is the SQL view of my query.

PARAMETERS [from] DateTime, [to] DateTime;
TRANSFORM Count(dbo_CallLog.ID) AS CountOfID
SELECT [AnsweredByFirstName] & " " & [answeredbylastname] AS Name
FROM dbo_CallLog
WHERE (((dbo_CallLog.StartTime) Between [forms]![frmGetDates_ESS]![dfrom] And [forms]![frmGetDates_ESS]![dto]) AND ((dbo_CallLog.Direction)=0) AND ((dbo_CallLog.Result)<3) AND ((dbo_CallLog.ToID)=3194 Or (dbo_CallLog.ToID)=5182) AND ((DateDiff("s",[starttime],[stoptime]))>0))
GROUP BY [AnsweredByFirstName] & " " & [answeredbylastname]
PIVOT Format([StartTime],"hh");

All I get is an error message:
"The Microsoft Jet database engine does not recognize '[forms]!frmGetDates_ESS]![dfrom.[Caption]' as a valid field name or expression"

Please help if you can. I am not sure if I can use a form to supply dates to a crosstab query. Thanks

Matthew

 

[PHV]

Replace this:
PARAMETERS [from] DateTime, [to] DateTime;
with this:
PARAMETERS [forms]![frmGetDates_ESS]![dfrom] DateTime, [forms]![frmGetDates_ESS]![dto] DateTime;

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

 

[MConlisk]

Ok, now it will go through with out an error. but I dont get any data. I can run the query without the form open, manually enter the dates in the pop-ups, and the data is there. But if I open the form and type the dates in the text boxes I get no data. Is there anything else I can try.

 

[MConlisk]

Ok. Thank you very much. I got it to work. I had to add .[value] to the end of the [forms]![frmGetDates_ESS]![dfrom] and [forms]![frmGetDates_ESS]![dto]. so now my SQL looks like this.

PARAMETERS [forms]![frmGetDates_ESS]![dfrom].[value] DateTime, [forms]![frmGetDates_ESS]![dto].[value] DateTime;
TRANSFORM Count(dbo_CallLog.ID) AS CountOfID
SELECT [AnsweredByFirstName] & " " & [answeredbylastname] AS Name
FROM dbo_CallLog
WHERE (((dbo_CallLog.StartTime) Between [forms]![frmGetDates_ESS]![dfrom].[value] And [forms]![frmGetDates_ESS]![dto].[value]) AND ((dbo_CallLog.Direction)=0) AND ((dbo_CallLog.Result)<3) AND ((dbo_CallLog.ToID)=3194 Or (dbo_CallLog.ToID)=5182) AND ((DateDiff("s",[starttime],[stoptime]))>0))
GROUP BY [AnsweredByFirstName] & " " & [answeredbylastname]
PIVOT Format([StartTime],"hh");

Thanks again

Matthew