Hopefull Final CrossTab Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I built a crosstab query (w/ out the Wizard - thanks Rick) that has days of
the week as the column heads that sum the amount of business done per day (by
hour - which is the rows). This works fine if there are sales to add for each
day. However, if there are zero sales that fit the criteria of that certain
day I get the error: 3070 "The Microsoft Jet database engine does not
recognize " as a valid field name or expression"
Thank you.
 
I assume you get the error when trying to preview the report. You need to
add your potential column heading values into the Column Headings property
of the crosstab query.
 
I have a similar problem, but the I am having the user enter the parameters
through a form. The parameter options will change in the future, it would
not be "ideal" to enter the Column Headings property. (I also tried to doing
this to test it out and I get a syntax error message.)

The odd thing is that I have set up a similar query/form/report and did not
get this error. I have tried to follow the same set-up but get the error
anyway.

Any ideas/suggestions?
 
I have defined my parameter data types (as text) in the query parameters
window. It also shows up in my SQL.
 
I am getting the same error as Scott:
The Microsoft Jet database engine does not recognize " as a valid field name
or expression. I get this error after clicking on the command button in my
form.
Here is the SQL for my crosstab query:

PARAMETERS [Forms]![frm_demoreport]![TestProd1] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd2] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd3] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd4] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd5] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd6] Text ( 255 );
TRANSFORM Max(tbl_demo_data2.SCORE) AS MaxOfSCORE
SELECT tbl_demo_data2.DEMO_GROUP, tbl_demo_data2.DEMO
FROM tbl_demo_data2
WHERE (((tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd1] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd2] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd3] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd4] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd5] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd6]))
GROUP BY tbl_demo_data2.DEMO_GROUP, tbl_demo_data2.DEMO
PIVOT tbl_demo_data2.PRODNM;
 
I don't see anything that stands out. I would try remove one criteria at a
time until it works.

--
Duane Hookom
MS Access MVP


HSL said:
I am getting the same error as Scott:
The Microsoft Jet database engine does not recognize " as a valid field
name
or expression. I get this error after clicking on the command button in
my
form.
Here is the SQL for my crosstab query:

PARAMETERS [Forms]![frm_demoreport]![TestProd1] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd2] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd3] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd4] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd5] Text ( 255 ),
[Forms]![frm_demoreport]![TestProd6] Text ( 255 );
TRANSFORM Max(tbl_demo_data2.SCORE) AS MaxOfSCORE
SELECT tbl_demo_data2.DEMO_GROUP, tbl_demo_data2.DEMO
FROM tbl_demo_data2
WHERE (((tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd1] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd2] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd3] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd4] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd5] Or
(tbl_demo_data2.PRODNM)=[Forms]![frm_demoreport]![TestProd6]))
GROUP BY tbl_demo_data2.DEMO_GROUP, tbl_demo_data2.DEMO
PIVOT tbl_demo_data2.PRODNM;


Duane Hookom said:
What error are you getting and what is the SQL of your query?
Click to expand...
Click to expand...
 
Back
Top