Parameter Dialog box

G

Guest

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");
 
G

Guest

It is exactly as I posted it from the SQL view of the query. I am not really
sure what you are asking me, sorry.


Pete D. said:
Are the quotes in the query as they shouldn't be.
FieldSalesRep:[Enter Sales Rep]

Arctic77 said:
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");
Click to expand...
Click to expand...
 
J

John W. Vinson

They are both
run off of Cross Tab Queries; nothing has changed, other than now I have to
put this in the Dialogue box.
Click to expand...

That's odd, because in my experience Crosstab queries have *always* required
explicit definition of the parameters!

Have you considered using form controls as your parameters? Instead of [Enter
search term:] you can use [Forms]![SomeFormName]![SomeControlName] as the
parameter. If you use a small unbound form to solicit the criteria, you can
put a command button on it to launch the Report or data-display form; the user
need not get even one prompt, much less two.

John W. Vinson [MVP]
 
G

Guest

Very Strange how we have different experiences. I will post the SQL code for
the original cross tab query that has parameters. As I wrote before, sadly I
din't know about the Parameter Dialogue box until yesterday.

Thank you for the suggestion about passing the parameter from a form
control, I will work on this and see if it makes any difference.

Here is the one that works fine.... ????? Now, there is one difference that
I am wondering about, the below query is run from a table as well as a query,
the code I posted in the original posting is getting it's data from a query
run off of a UNION query. Can this be the difference in why I have to
explicitly define the parameter?


TRANSFORM Sum([$CalcSales].sh_shipamount) AS SumOfsh_shipamount
SELECT [$CalcSales].cl_CustName, DB_CUSTOMERS.cl_Province,
[$CalcSales].cl_Phone, [$CalcSales].cl_SalesMan, [$CalcSales].sh_itemcode,
qry_cat_Full.de_descrip1, qry_cat_Full.de_descrip2, qry_cat_Full.de_dealer,
qry_cat_Full.sup_UPC, qry_cat_Full.Header, qry_cat_Full.DeptLong,
[$CalcSales].cl_SalesMan, qry_cat_Full.de_vcode,
Sum([$CalcSales].sh_shipamount) AS [Total Of sh_shipamount]
FROM ([$CalcSales] INNER JOIN qry_cat_Full ON [$CalcSales].sh_itemcode =
qry_cat_Full.de_code) LEFT JOIN DB_CUSTOMERS ON [$CalcSales].sh_cust_code =
DB_CUSTOMERS.cl_CustCode
WHERE ((([$CalcSales].cl_SalesMan)=[enter salesman code]))
GROUP BY [$CalcSales].cl_CustName, DB_CUSTOMERS.cl_Province,
[$CalcSales].cl_Phone, [$CalcSales].cl_SalesMan, [$CalcSales].sh_itemcode,
qry_cat_Full.de_descrip1, qry_cat_Full.de_descrip2, qry_cat_Full.de_dealer,
qry_cat_Full.sup_UPC, qry_cat_Full.Header, qry_cat_Full.DeptLong,
[$CalcSales].cl_SalesMan, qry_cat_Full.de_vcode
PIVOT Format([sh_Transdate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");




John W. Vinson said:
They are both
run off of Cross Tab Queries; nothing has changed, other than now I have to
put this in the Dialogue box.
Click to expand...

That's odd, because in my experience Crosstab queries have *always* required
explicit definition of the parameters!

Have you considered using form controls as your parameters? Instead of [Enter
search term:] you can use [Forms]![SomeFormName]![SomeControlName] as the
parameter. If you use a small unbound form to solicit the criteria, you can
put a command button on it to launch the Report or data-display form; the user
need not get even one prompt, much less two.

John W. Vinson [MVP]
Click to expand...
 
P

Pete D.

I'm so sorry, I didn't see the SQL at the end of your email, I blame age.
Although I can do this, John can do a much better job if you want to get
there this month. Pete D.
Arctic77 said:
It is exactly as I posted it from the SQL view of the query. I am not
really
sure what you are asking me, sorry.


Pete D. said:
Are the quotes in the query as they shouldn't be.
FieldSalesRep:[Enter Sales Rep]

Arctic77 said:
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");
Click to expand...
Click to expand...
Click to expand...
 
Top