A
Arctic77
We have recently switched front end systems and our back end reporting is
based on an Access database that I am currently switching over. My question
is... I have had many parameter queries without explicitly entering them into
the parameter box. Is there a reason that I am getting the error "The
Microsoft Jet database engine does not recognize'[Enter Sales Rep]" as a
valid field name or expression. This is driving me nuts as I am only
duplicating queries that were already built on the old system. They are both
run off of Cross Tab Queries; nothing has changed, other than now I have to
put this in the Dialogue box. The reason that I need an answer is because I
am running into hassles when building reports off this query; it asks me
twice for the parameter even when I am just opening the report up in design
view and when saving the report. Very frustrating as I have NEVER done this,
in fact, embarrassingly I didn't know until yesterday that the Parameter
Declaration box was even there.
FYI the SQL code is....
PARAMETERS [Enter Sales Rep] Text ( 255 );
TRANSFORM Sum(qry_CalcSalesnoPL.sh_shipamount) AS SumOfsh_shipamount
SELECT qry_CalcSalesnoPL.cl_custName, js_customers.cl_city,
qry_CalcSalesnoPL.cl_Province, qry_CalcSalesnoPL.sh_itemcode,
cat_Catalogue_Full.de_vcode, qry_CalcSalesnoPL.cl_Phone,
qry_CalcSalesnoPL.de_descrip1, cat_Catalogue_Full.de_dealer,
cat_Catalogue_Full.DE_upc, cat_Catalogue_Full.New, cat_Catalogue_Full.Header,
cat_Catalogue_Full.DeptLong, Sum(qry_CalcSalesnoPL.sh_shipamount) AS [Total
Of sh_shipamount]
FROM (qry_CalcSalesnoPL LEFT JOIN js_customers ON
qry_CalcSalesnoPL.sh_cust_code = js_customers.cl_CustCode) INNER JOIN
cat_Catalogue_Full ON qry_CalcSalesnoPL.sh_itemcode =
cat_Catalogue_Full.de_code
WHERE (((qry_CalcSalesnoPL.cl_SalesMan)=[Enter Sales Rep]))
GROUP BY qry_CalcSalesnoPL.cl_custName, js_customers.cl_city,
qry_CalcSalesnoPL.cl_Province, qry_CalcSalesnoPL.sh_itemcode,
cat_Catalogue_Full.de_vcode, qry_CalcSalesnoPL.cl_Phone,
qry_CalcSalesnoPL.de_descrip1, cat_Catalogue_Full.de_dealer,
cat_Catalogue_Full.DE_upc, cat_Catalogue_Full.New, cat_Catalogue_Full.Header,
cat_Catalogue_Full.DeptLong
PIVOT Format([sh_transdate],"yyyy");
based on an Access database that I am currently switching over. My question
is... I have had many parameter queries without explicitly entering them into
the parameter box. Is there a reason that I am getting the error "The
Microsoft Jet database engine does not recognize'[Enter Sales Rep]" as a
valid field name or expression. This is driving me nuts as I am only
duplicating queries that were already built on the old system. They are both
run off of Cross Tab Queries; nothing has changed, other than now I have to
put this in the Dialogue box. The reason that I need an answer is because I
am running into hassles when building reports off this query; it asks me
twice for the parameter even when I am just opening the report up in design
view and when saving the report. Very frustrating as I have NEVER done this,
in fact, embarrassingly I didn't know until yesterday that the Parameter
Declaration box was even there.
FYI the SQL code is....
PARAMETERS [Enter Sales Rep] Text ( 255 );
TRANSFORM Sum(qry_CalcSalesnoPL.sh_shipamount) AS SumOfsh_shipamount
SELECT qry_CalcSalesnoPL.cl_custName, js_customers.cl_city,
qry_CalcSalesnoPL.cl_Province, qry_CalcSalesnoPL.sh_itemcode,
cat_Catalogue_Full.de_vcode, qry_CalcSalesnoPL.cl_Phone,
qry_CalcSalesnoPL.de_descrip1, cat_Catalogue_Full.de_dealer,
cat_Catalogue_Full.DE_upc, cat_Catalogue_Full.New, cat_Catalogue_Full.Header,
cat_Catalogue_Full.DeptLong, Sum(qry_CalcSalesnoPL.sh_shipamount) AS [Total
Of sh_shipamount]
FROM (qry_CalcSalesnoPL LEFT JOIN js_customers ON
qry_CalcSalesnoPL.sh_cust_code = js_customers.cl_CustCode) INNER JOIN
cat_Catalogue_Full ON qry_CalcSalesnoPL.sh_itemcode =
cat_Catalogue_Full.de_code
WHERE (((qry_CalcSalesnoPL.cl_SalesMan)=[Enter Sales Rep]))
GROUP BY qry_CalcSalesnoPL.cl_custName, js_customers.cl_city,
qry_CalcSalesnoPL.cl_Province, qry_CalcSalesnoPL.sh_itemcode,
cat_Catalogue_Full.de_vcode, qry_CalcSalesnoPL.cl_Phone,
qry_CalcSalesnoPL.de_descrip1, cat_Catalogue_Full.de_dealer,
cat_Catalogue_Full.DE_upc, cat_Catalogue_Full.New, cat_Catalogue_Full.Header,
cat_Catalogue_Full.DeptLong
PIVOT Format([sh_transdate],"yyyy");