Pivot Query Slow with Dates Criteria

[erotisi via AccessMonster.com]

Hello all,

I have a query which, among others, accepts from an input form two date
parameters Appointm_From and Appointm_To as criteria.
The initial values of the parameters are Appointm_From = 01/01/1900 and
Appointm_To = today.
Results are produced in datasheet view very fast and if then is tranformed to
pivot view this is also very fast.
However when the user changes the above initial values of dates on the input
form, then results are still produced fast in datasheet view, but when
transformed to pivot view it is extrememely slow and most of the times
tranformation to pivot never succeeds. Even when we are talking for only
about 100 lines of data.
I have tested that the problem is not connected to the rest of the parameters,
but rather only to those dates.

I am sending the where statement also, though it is a bit complex....

Regards to all,

Peri


The where statement is like this:

WHERE (((Serial_No.Serial_No) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]!
[CmbSerialNo])) AND ((Material.Mat_Prod_Group_Code) Like ("*" & [Forms]!
[FrmSerial_No_Actions_rep]![CmbProdgroup])) AND ((Serial_No.Resp_Sector) Like
("*" & [Forms]![FrmSerial_No_Actions_rep]![CmbRespSect])) AND ((Actions.
Status) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]![CmbStatus])) AND (
(Actions.Action_Type) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]!
[CmbActionType])) AND ((Actions.Appointm_From) Is Null) AND ((Actions.
Appointm_To) Is Null) AND ((Serial_No.EndUser_Cust_Code) Like ("*" & [Forms]!
[FrmSerial_No_Actions_rep]![CmdEndCust])) AND ((Serial_No.
Bill_to_Customer_Code) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]!
[CmbBilltoCust]))) OR (((Serial_No.Serial_No) Like ("*" & [Forms]!
[FrmSerial_No_Actions_rep]![CmbSerialNo])) AND ((Material.Mat_Prod_Group_Code)
Like ("*" & [Forms]![FrmSerial_No_Actions_rep]![CmbProdgroup])) AND (
(Serial_No.Resp_Sector) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]!
[CmbRespSect])) AND ((Actions.Status) Like ("*" & [Forms]!
[FrmSerial_No_Actions_rep]![CmbStatus])) AND ((Actions.Action_Type) Like ("*"
& [Forms]![FrmSerial_No_Actions_rep]![CmbActionType])) AND ((Actions.
Appointm_From)>=[Forms]![FrmSerial_No_Actions_rep]![TxtAppFromDate]) AND (
(Actions.Appointm_To)<=[Forms]![FrmSerial_No_Actions_rep]![TxtApptoDate]) AND
((Serial_No.EndUser_Cust_Code) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]!
[CmdEndCust])) AND ((Serial_No.Bill_to_Customer_Code) Like ("*" & [Forms]!
[FrmSerial_No_Actions_rep]![CmbBilltoCust]))) OR (((Serial_No.Serial_No) Like
("*" & [Forms]![FrmSerial_No_Actions_rep]![CmbSerialNo])) AND ((Material.
Mat_Prod_Group_Code) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]!
[CmbProdgroup])) AND ((Serial_No.Resp_Sector) Like ("*" & [Forms]!
[FrmSerial_No_Actions_rep]![CmbRespSect])) AND ((Actions.Status) Like ("*" &
[Forms]![FrmSerial_No_Actions_rep]![CmbStatus])) AND ((Actions.Action_Type)
Like ("*" & [Forms]![FrmSerial_No_Actions_rep]![CmbActionType])) AND (
(Actions.Appointm_From)>=[Forms]![FrmSerial_No_Actions_rep]![TxtAppFromDate])
AND ((Actions.Appointm_To) Is Null) AND ((Serial_No.EndUser_Cust_Code) Like
("*" & [Forms]![FrmSerial_No_Actions_rep]![CmdEndCust])) AND ((Serial_No.
Bill_to_Customer_Code) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]!
[CmbBilltoCust]))) OR (((Serial_No.Serial_No) Like ("*" & [Forms]!
[FrmSerial_No_Actions_rep]![CmbSerialNo])) AND ((Material.Mat_Prod_Group_Code)
Like ("*" & [Forms]![FrmSerial_No_Actions_rep]![CmbProdgroup])) AND (
(Serial_No.Resp_Sector) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]!
[CmbRespSect])) AND ((Actions.Status) Like ("*" & [Forms]!
[FrmSerial_No_Actions_rep]![CmbStatus])) AND ((Actions.Action_Type) Like ("*"
& [Forms]![FrmSerial_No_Actions_rep]![CmbActionType])) AND ((Actions.
Appointm_From) Is Null) AND ((Actions.Appointm_To)<=[Forms]!
[FrmSerial_No_Actions_rep]![TxtApptoDate]) AND ((Serial_No.EndUser_Cust_Code)
Like ("*" & [Forms]![FrmSerial_No_Actions_rep]![CmdEndCust])) AND ((Serial_No.
Bill_to_Customer_Code) Like ("*" & [Forms]![FrmSerial_No_Actions_rep]!
[CmbBilltoCust])))
ORDER BY Serial_No.Resp_Sector, Actions.Appointm_To DESC , Customers.
Customer_Name, Serial_No.Instal_City, Serial_No.Instal_Addr, Serial_No.
Serial_No;