Crosstab query and report

  • Thread starter Thread starter Thomas Kroljic
  • Start date Start date
T

Thomas Kroljic

All,
I have a crosstab query that works OK when I manually run it. Here is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending Date] DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And [Enter Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record source to a
report. First off, I get prompted
four time for the input fields instead of just two. Then I get this message
from Access 2002:

"The Microsoft Jet database engine does not recognize " as a valid field
name or expression"

Again, if I run the query manually, it prompts me for two input values and
display the correct data, but when I
try to assign it to a new report, I get errors.

Any suggestions as to what I might be doing wrong or missing?

Thank you,
Thomas Kroljic
 
I would get the criteria from controls on a form rather than parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static set of
CallCodeIDs then you should enter them into the Column Headings property.
 
Duane,
I've tried several times to use a form with controls the user can
fill-in before running the report with the crosstab query, but I get an
error message stating the forms!myform!txt is not a field or expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of queries. It's
when I open the report and try to run the report that I have problems.

Even if I stay with parameters in the query, why would the system prompt me
double the amount of parameters that I've have listed. It's as if during
each phase of the report opening up I get prompted for all parameters. What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

Duane Hookom said:
I would get the criteria from controls on a form rather than parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static set of
CallCodeIDs then you should enter them into the Column Headings property.

--
Duane Hookom
MS Access MVP


Thomas Kroljic said:
All,
I have a crosstab query that works OK when I manually run it. Here is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending Date] DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And [Enter Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record source to a
report. First off, I get prompted
four time for the input fields instead of just two. Then I get this message
from Access 2002:

"The Microsoft Jet database engine does not recognize " as a valid field
name or expression"

Again, if I run the query manually, it prompts me for two input values and
display the correct data, but when I
try to assign it to a new report, I get errors.

Any suggestions as to what I might be doing wrong or missing?

Thank you,
Thomas Kroljic
Click to expand...
Click to expand...
 
Printing a report based on queries may cause the report to "requery" thus
asking for the same values again. That is one of the reasons that I always
get values from controls on forms. This has always worked for me.

Did you set the column headings property?

--
Duane Hookom
MS Access MVP


Thomas Kroljic said:
Duane,
I've tried several times to use a form with controls the user can
fill-in before running the report with the crosstab query, but I get an
error message stating the forms!myform!txt is not a field or expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of queries. It's
when I open the report and try to run the report that I have problems.

Even if I stay with parameters in the query, why would the system prompt me
double the amount of parameters that I've have listed. It's as if during
each phase of the report opening up I get prompted for all parameters. What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

Duane Hookom said:
I would get the criteria from controls on a form rather than parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static set of
CallCodeIDs then you should enter them into the Column Headings property.

--
Duane Hookom
MS Access MVP


Thomas Kroljic said:
All,
I have a crosstab query that works OK when I manually run it. Here is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending Date] DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And [Enter Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record source
Click to expand...
Click to expand...
to
a
Click to expand...
Click to expand...
 
Thomas said:
I've tried several times to use a form with controls the user can
fill-in before running the report with the crosstab query, but I get an
error message stating the forms!myform!txt is not a field or expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of queries. It's
when I open the report and try to run the report that I have problems.

Even if I stay with parameters in the query, why would the system prompt me
double the amount of parameters that I've have listed. It's as if during
each phase of the report opening up I get prompted for all parameters. What
would cause this?
Click to expand...

I thought that you had to explicitly declare the parameters
for a crosstab query?
 
Duane,
First off, thanks for taking the time to help me out. I do appreciate
it.

I have a book called "Access 2000 Developers handbook Vol 1" by Ken
Getz, Paul Litwin and Mike Gilbert. This is my bible.

In chapter 9 there is an example of a crosstab report. This is the
report I am using. It also contains some VBA code which I do understand.

I've copied (imported) his report from the CD that came with the
book into my database. I added four additional labels, text, and sum text
fields to the report. I then added my query see below:

PARAMETERS [forms]![myform]![beginningcalldate] DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
totalcount
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
***** WHERE (((Calls.CallDate)>[forms]![myform]![beginningcalldate]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;


Duane, If I remove the where clause, the report runs great. If I include the
where clause (and I added the parameter definition to the query), I get an
empty report. No data. If I use the debugger to follow the code this is what
I noticed. At the time the VBA code assign the record source to a new rst
object (see code segment below) , when the code his the line "intColCount =
rst.Fields.Count", the value is 0. Again, this works perfectly if I take out
the where clause. At this point, I'm lost. I've search google looking for
clues. I think I have my blinders on because I can not see what is causing
this problem. Hopefully this data will help.

rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

Thank you,
Thomas Kroljic


Duane Hookom said:
Printing a report based on queries may cause the report to "requery" thus
asking for the same values again. That is one of the reasons that I always
get values from controls on forms. This has always worked for me.

Did you set the column headings property?

--
Duane Hookom
MS Access MVP


Thomas Kroljic said:
Duane,
I've tried several times to use a form with controls the user can
fill-in before running the report with the crosstab query, but I get an
error message stating the forms!myform!txt is not a field or expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of queries. It's
when I open the report and try to run the report that I have problems.

Even if I stay with parameters in the query, why would the system prompt me
double the amount of parameters that I've have listed. It's as if during
each phase of the report opening up I get prompted for all parameters. What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

Duane Hookom said:
I would get the criteria from controls on a form rather than parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static set of
CallCodeIDs then you should enter them into the Column Headings property.

--
Duane Hookom
MS Access MVP


All,
I have a crosstab query that works OK when I manually run it.
Click to expand...
Here
is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending Date]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And [Enter
Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record source
Click to expand...
to
a
report. First off, I get prompted
four time for the input fields instead of just two. Then I get this
message
from Access 2002:

"The Microsoft Jet database engine does not recognize " as a valid field
name or expression"

Again, if I run the query manually, it prompts me for two input
Click to expand...
values
and
display the correct data, but when I
try to assign it to a new report, I get errors.

Any suggestions as to what I might be doing wrong or missing?

Thank you,
Thomas Kroljic
Click to expand...
Click to expand...
Click to expand...
 
Marsh,
Thanks for the reply. Please look at my last posting to Duane. I'm
hitting the wall at this point.
I'm using a well documented report from the "Access 2000 Developers
Handbook" by Ken Getz.
Like I mentioned to Duane, it works fine with a normal crosstab query. Once
I add a parameter. I get a blank, empty report. My parameter is a reference
to another form ( [forms]![myform]![textfield] ) that is open at the time of
the report.

Any suggestions would be greatly appreciated.

Thank you,
Thomas J. Kroljic
 
Thomas said:
Marsh,
Thanks for the reply. Please look at my last posting to Duane. I'm
hitting the wall at this point.
I'm using a well documented report from the "Access 2000 Developers
Handbook" by Ken Getz.
Like I mentioned to Duane, it works fine with a normal crosstab query. Once
I add a parameter. I get a blank, empty report. My parameter is a reference
to another form ( [forms]![myform]![textfield] ) that is open at the time of
the report.

Any suggestions would be greatly appreciated.
Click to expand...


I don't use ADO so I won't be able add any value to this
discussion.
 
No offense to Getz, Litwin, and Gilbert but I don't like their solution. I
created and use the much more flexible (less code) solution for crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thomas Kroljic said:
Duane,
First off, thanks for taking the time to help me out. I do appreciate
it.

I have a book called "Access 2000 Developers handbook Vol 1" by Ken
Getz, Paul Litwin and Mike Gilbert. This is my bible.

In chapter 9 there is an example of a crosstab report. This is the
report I am using. It also contains some VBA code which I do understand.

I've copied (imported) his report from the CD that came with the
book into my database. I added four additional labels, text, and sum text
fields to the report. I then added my query see below:

PARAMETERS [forms]![myform]![beginningcalldate] DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
totalcount
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
***** WHERE (((Calls.CallDate)>[forms]![myform]![beginningcalldate]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;


Duane, If I remove the where clause, the report runs great. If I include the
where clause (and I added the parameter definition to the query), I get an
empty report. No data. If I use the debugger to follow the code this is what
I noticed. At the time the VBA code assign the record source to a new rst
object (see code segment below) , when the code his the line "intColCount =
rst.Fields.Count", the value is 0. Again, this works perfectly if I take out
the where clause. At this point, I'm lost. I've search google looking for
clues. I think I have my blinders on because I can not see what is causing
this problem. Hopefully this data will help.

rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

Thank you,
Thomas Kroljic


Duane Hookom said:
Printing a report based on queries may cause the report to "requery" thus
asking for the same values again. That is one of the reasons that I always
get values from controls on forms. This has always worked for me.

Did you set the column headings property?

--
Duane Hookom
MS Access MVP


Thomas Kroljic said:
Duane,
I've tried several times to use a form with controls the user can
fill-in before running the report with the crosstab query, but I get an
error message stating the forms!myform!txt is not a field or expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of queries. It's
when I open the report and try to run the report that I have problems.

Even if I stay with parameters in the query, why would the system
Click to expand...
prompt
me
double the amount of parameters that I've have listed. It's as if during
each phase of the report opening up I get prompted for all parameters. What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

I would get the criteria from controls on a form rather than parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static set of
CallCodeIDs then you should enter them into the Column Headings property.

--
Duane Hookom
MS Access MVP


All,
I have a crosstab query that works OK when I manually run it. Here
is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending Date]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And [Enter
Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record
Click to expand...
source
to
a
report. First off, I get prompted
four time for the input fields instead of just two. Then I get this
message
from Access 2002:

"The Microsoft Jet database engine does not recognize " as a valid field
name or expression"

Again, if I run the query manually, it prompts me for two input values
and
display the correct data, but when I
try to assign it to a new report, I get errors.

Any suggestions as to what I might be doing wrong or missing?

Thank you,
Thomas Kroljic
Click to expand...
Click to expand...
Click to expand...
 
Duane,
I'll download the file from the link you provide and review it.

Thanks,
Thomas J. Kroljic

Duane Hookom said:
No offense to Getz, Litwin, and Gilbert but I don't like their solution. I
created and use the much more flexible (less code) solution for crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thomas Kroljic said:
Duane,
First off, thanks for taking the time to help me out. I do appreciate
it.

I have a book called "Access 2000 Developers handbook Vol 1" by Ken
Getz, Paul Litwin and Mike Gilbert. This is my bible.

In chapter 9 there is an example of a crosstab report. This is the
report I am using. It also contains some VBA code which I do understand.

I've copied (imported) his report from the CD that came with the
book into my database. I added four additional labels, text, and sum text
fields to the report. I then added my query see below:

PARAMETERS [forms]![myform]![beginningcalldate] DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
totalcount
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
***** WHERE (((Calls.CallDate)>[forms]![myform]![beginningcalldate]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;


Duane, If I remove the where clause, the report runs great. If I include the
where clause (and I added the parameter definition to the query), I get an
empty report. No data. If I use the debugger to follow the code this is what
I noticed. At the time the VBA code assign the record source to a new rst
object (see code segment below) , when the code his the line
Click to expand...
"intColCount
=
rst.Fields.Count", the value is 0. Again, this works perfectly if I take out
the where clause. At this point, I'm lost. I've search google looking for
clues. I think I have my blinders on because I can not see what is causing
this problem. Hopefully this data will help.

rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

Thank you,
Thomas Kroljic


Duane Hookom said:
Printing a report based on queries may cause the report to "requery" thus
asking for the same values again. That is one of the reasons that I always
get values from controls on forms. This has always worked for me.

Did you set the column headings property?

--
Duane Hookom
MS Access MVP


Duane,
I've tried several times to use a form with controls the user can
fill-in before running the report with the crosstab query, but I get an
error message stating the forms!myform!txt is not a field or expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of
Click to expand...
queries.
It's
when I open the report and try to run the report that I have problems.

Even if I stay with parameters in the query, why would the system prompt
me
double the amount of parameters that I've have listed. It's as if during
each phase of the report opening up I get prompted for all parameters.
What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

I would get the criteria from controls on a form rather than parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static
Click to expand...
set
of
CallCodeIDs then you should enter them into the Column Headings
property.

--
Duane Hookom
MS Access MVP


All,
I have a crosstab query that works OK when I manually run
Click to expand...
it.
Here
is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending Date]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And [Enter
Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record source
to
a
report. First off, I get prompted
four time for the input fields instead of just two. Then I get this
message
from Access 2002:

"The Microsoft Jet database engine does not recognize " as a valid
field
name or expression"

Again, if I run the query manually, it prompts me for two input values
and
display the correct data, but when I
try to assign it to a new report, I get errors.

Any suggestions as to what I might be doing wrong or missing?

Thank you,
Thomas Kroljic
Click to expand...
Click to expand...
Click to expand...
 
Duane,
I download your example database called CrossTab.mdb.
I noticed several things that are different:

1. the use of a initial query to rebuild the data table
(qappEmpCust) using the start and end date from your
frmReportSelect form.
2. In your query (qxtbEmpCustSales) I notice the use of the where
clause. What I found surprising was the absent of a parameter definition via
Query/Parameter and no "parameter" syntax within the SQL statement itself.
Why doesn't this query need to define the parameters? If I remove
the parameter statement from my query I get an Microsoft error message "The
MS Jet database engine does not recognize
'[forms]![frmReportSelect]![txtstartdate]' as a valid field name or
expression.

I think I've had enough headaches with this query. Tomorrow I plan
on doing the following to accomplish my end results: first, I'll execute a
query that creates a table with the appropriate data based on the start and
end dates from a user input form. Next, I'll create a general crosstab query
against this table that will be used by my original report (the one out of
the "Access 2000 Developers Handbook).

I still can't comprehend why your query doesn't need a parameter
statement and mine does. Any thoughts on this?

Again, thank you for all your help and input. I do appreciate it. And I
do eventually learn from my mistakes :)
And I do learn from examples such as CrossTab.mdb

Thank you,
Thomas J. Kroljic


Duane Hookom said:
No offense to Getz, Litwin, and Gilbert but I don't like their solution. I
created and use the much more flexible (less code) solution for crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thomas Kroljic said:
Duane,
First off, thanks for taking the time to help me out. I do appreciate
it.

I have a book called "Access 2000 Developers handbook Vol 1" by Ken
Getz, Paul Litwin and Mike Gilbert. This is my bible.

In chapter 9 there is an example of a crosstab report. This is the
report I am using. It also contains some VBA code which I do understand.

I've copied (imported) his report from the CD that came with the
book into my database. I added four additional labels, text, and sum text
fields to the report. I then added my query see below:

PARAMETERS [forms]![myform]![beginningcalldate] DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
totalcount
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
***** WHERE (((Calls.CallDate)>[forms]![myform]![beginningcalldate]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;


Duane, If I remove the where clause, the report runs great. If I include the
where clause (and I added the parameter definition to the query), I get an
empty report. No data. If I use the debugger to follow the code this is what
I noticed. At the time the VBA code assign the record source to a new rst
object (see code segment below) , when the code his the line
Click to expand...
"intColCount
=
rst.Fields.Count", the value is 0. Again, this works perfectly if I take out
the where clause. At this point, I'm lost. I've search google looking for
clues. I think I have my blinders on because I can not see what is causing
this problem. Hopefully this data will help.

rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

Thank you,
Thomas Kroljic


Duane Hookom said:
Printing a report based on queries may cause the report to "requery" thus
asking for the same values again. That is one of the reasons that I always
get values from controls on forms. This has always worked for me.

Did you set the column headings property?

--
Duane Hookom
MS Access MVP


Duane,
I've tried several times to use a form with controls the user can
fill-in before running the report with the crosstab query, but I get an
error message stating the forms!myform!txt is not a field or expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of
Click to expand...
queries.
It's
when I open the report and try to run the report that I have problems.

Even if I stay with parameters in the query, why would the system prompt
me
double the amount of parameters that I've have listed. It's as if during
each phase of the report opening up I get prompted for all parameters.
What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

I would get the criteria from controls on a form rather than parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static
Click to expand...
set
of
CallCodeIDs then you should enter them into the Column Headings
property.

--
Duane Hookom
MS Access MVP


All,
I have a crosstab query that works OK when I manually run
Click to expand...
it.
Here
is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending Date]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And [Enter
Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record source
to
a
report. First off, I get prompted
four time for the input fields instead of just two. Then I get this
message
from Access 2002:

"The Microsoft Jet database engine does not recognize " as a valid
field
name or expression"

Again, if I run the query manually, it prompts me for two input values
and
display the correct data, but when I
try to assign it to a new report, I get errors.

Any suggestions as to what I might be doing wrong or missing?

Thank you,
Thomas Kroljic
Click to expand...
Click to expand...
Click to expand...
 
1. Your code would make a combination of CallCodeID and Store. The append
query makes sure all possible combinations of these two for the date range
are included.
2. If you take out the Column Headings property value
"A","B","C","D","E","F","G","H", then you will get the error.

--
Duane Hookom
MS Access MVP


Thomas Kroljic said:
Duane,
I download your example database called CrossTab.mdb.
I noticed several things that are different:

1. the use of a initial query to rebuild the data table
(qappEmpCust) using the start and end date from your
frmReportSelect form.
2. In your query (qxtbEmpCustSales) I notice the use of the where
clause. What I found surprising was the absent of a parameter definition via
Query/Parameter and no "parameter" syntax within the SQL statement itself.
Why doesn't this query need to define the parameters? If I remove
the parameter statement from my query I get an Microsoft error message "The
MS Jet database engine does not recognize
'[forms]![frmReportSelect]![txtstartdate]' as a valid field name or
expression.

I think I've had enough headaches with this query. Tomorrow I plan
on doing the following to accomplish my end results: first, I'll execute a
query that creates a table with the appropriate data based on the start and
end dates from a user input form. Next, I'll create a general crosstab query
against this table that will be used by my original report (the one out of
the "Access 2000 Developers Handbook).

I still can't comprehend why your query doesn't need a parameter
statement and mine does. Any thoughts on this?

Again, thank you for all your help and input. I do appreciate it. And I
do eventually learn from my mistakes :)
And I do learn from examples such as CrossTab.mdb

Thank you,
Thomas J. Kroljic


Duane Hookom said:
No offense to Getz, Litwin, and Gilbert but I don't like their solution. I
created and use the much more flexible (less code) solution for crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thomas Kroljic said:
Duane,
First off, thanks for taking the time to help me out. I do appreciate
it.

I have a book called "Access 2000 Developers handbook Vol 1" by Ken
Getz, Paul Litwin and Mike Gilbert. This is my bible.

In chapter 9 there is an example of a crosstab report. This is the
report I am using. It also contains some VBA code which I do understand.

I've copied (imported) his report from the CD that came with the
book into my database. I added four additional labels, text, and sum text
fields to the report. I then added my query see below:

PARAMETERS [forms]![myform]![beginningcalldate] DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
totalcount
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
***** WHERE (((Calls.CallDate)>[forms]![myform]![beginningcalldate]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;


Duane, If I remove the where clause, the report runs great. If I
Click to expand...
include
the
where clause (and I added the parameter definition to the query), I
Click to expand...
Click to expand...
get
an
empty report. No data. If I use the debugger to follow the code this
Click to expand...
is
what
I noticed. At the time the VBA code assign the record source to a new rst
object (see code segment below) , when the code his the line
Click to expand...
"intColCount
=
rst.Fields.Count", the value is 0. Again, this works perfectly if I
Click to expand...
take
out
the where clause. At this point, I'm lost. I've search google looking for
clues. I think I have my blinders on because I can not see what is causing
this problem. Hopefully this data will help.

rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

Thank you,
Thomas Kroljic


Printing a report based on queries may cause the report to "requery" thus
asking for the same values again. That is one of the reasons that I always
get values from controls on forms. This has always worked for me.

Did you set the column headings property?

--
Duane Hookom
MS Access MVP


Duane,
I've tried several times to use a form with controls the user can
fill-in before running the report with the crosstab query, but I
Click to expand...
get
an
error message stating the forms!myform!txt is not a field or expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of queries.
It's
when I open the report and try to run the report that I have problems.

Even if I stay with parameters in the query, why would the system prompt
me
double the amount of parameters that I've have listed. It's as if during
each phase of the report opening up I get prompted for all parameters.
What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

I would get the criteria from controls on a form rather than parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static set
of
CallCodeIDs then you should enter them into the Column Headings
property.

--
Duane Hookom
MS Access MVP


All,
I have a crosstab query that works OK when I manually run it.
Here
is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending Date]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And [Enter
Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record source
to
a
report. First off, I get prompted
four time for the input fields instead of just two. Then I get this
message
from Access 2002:

"The Microsoft Jet database engine does not recognize " as a valid
field
name or expression"

Again, if I run the query manually, it prompts me for two input
values
and
display the correct data, but when I
try to assign it to a new report, I get errors.

Any suggestions as to what I might be doing wrong or missing?

Thank you,
Thomas Kroljic
Click to expand...
Click to expand...
Click to expand...
 
Duane,
Based on your second comment below, I added the "in" syntax to the end
of my crosstab query with the proper codes and now the query with the report
works fine. THANK YOU!!!

I looked up the "IN" clause in some of my Access books, but they really
do not describe the importance or the effect that this clause can have. It
appears that the "IN" clause can help you control the column headings,
especially the display order of the columns.

So why does adding the "IN" clause to the crosstab query eliminate the
need to have a parameter definition?

Thanks for all your help.

Thomas J. Kroljic

Duane Hookom said:
1. Your code would make a combination of CallCodeID and Store. The append
query makes sure all possible combinations of these two for the date range
are included.
2. If you take out the Column Headings property value
"A","B","C","D","E","F","G","H", then you will get the error.

--
Duane Hookom
MS Access MVP


Thomas Kroljic said:
Duane,
I download your example database called CrossTab.mdb.
I noticed several things that are different:

1. the use of a initial query to rebuild the data table
(qappEmpCust) using the start and end date from your
frmReportSelect form.
2. In your query (qxtbEmpCustSales) I notice the use of the where
clause. What I found surprising was the absent of a parameter definition via
Query/Parameter and no "parameter" syntax within the SQL statement itself.
Why doesn't this query need to define the parameters? If I remove
the parameter statement from my query I get an Microsoft error message "The
MS Jet database engine does not recognize
'[forms]![frmReportSelect]![txtstartdate]' as a valid field name or
expression.

I think I've had enough headaches with this query. Tomorrow I plan
on doing the following to accomplish my end results: first, I'll execute a
query that creates a table with the appropriate data based on the start and
end dates from a user input form. Next, I'll create a general crosstab query
against this table that will be used by my original report (the one out of
the "Access 2000 Developers Handbook).

I still can't comprehend why your query doesn't need a parameter
statement and mine does. Any thoughts on this?

Again, thank you for all your help and input. I do appreciate it.
Click to expand...
And
I
do eventually learn from my mistakes :)
And I do learn from examples such as CrossTab.mdb

Thank you,
Thomas J. Kroljic
Click to expand...
solution.
I
created and use the much more flexible (less code) solution for crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Duane,
First off, thanks for taking the time to help me out. I do appreciate
it.

I have a book called "Access 2000 Developers handbook Vol 1" by Ken
Getz, Paul Litwin and Mike Gilbert. This is my bible.

In chapter 9 there is an example of a crosstab report. This
Click to expand...
is
the
report I am using. It also contains some VBA code which I do understand.

I've copied (imported) his report from the CD that came with the
book into my database. I added four additional labels, text, and sum text
fields to the report. I then added my query see below:

PARAMETERS [forms]![myform]![beginningcalldate] DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
totalcount
FROM Calls INNER JOIN Store ON Calls.StoreID =
Store.StoreId
***** WHERE (((Calls.CallDate)>[forms]![myform]![beginningcalldate]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;


Duane, If I remove the where clause, the report runs great. If I include
the
where clause (and I added the parameter definition to the query), I
Click to expand...
get
an
empty report. No data. If I use the debugger to follow the code this is
what
I noticed. At the time the VBA code assign the record source to a
Click to expand...
new
rst
object (see code segment below) , when the code his the line "intColCount
=
rst.Fields.Count", the value is 0. Again, this works perfectly if I take
out
the where clause. At this point, I'm lost. I've search google
Click to expand...
looking
for
clues. I think I have my blinders on because I can not see what is causing
this problem. Hopefully this data will help.

rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

Thank you,
Thomas Kroljic


Printing a report based on queries may cause the report to "requery"
thus
asking for the same values again. That is one of the reasons that I
always
get values from controls on forms. This has always worked for me.

Did you set the column headings property?

--
Duane Hookom
MS Access MVP


Duane,
I've tried several times to use a form with controls the
Click to expand...
user
can
fill-in before running the report with the crosstab query, but I get
an
error message stating the forms!myform!txt is not a field or
expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of queries.
It's
when I open the report and try to run the report that I have problems.

Even if I stay with parameters in the query, why would the system
prompt
me
double the amount of parameters that I've have listed. It's as if
during
each phase of the report opening up I get prompted for all parameters.
What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

I would get the criteria from controls on a form rather than
parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a
Click to expand...
static
set
of
CallCodeIDs then you should enter them into the Column Headings
property.

--
Duane Hookom
MS Access MVP


All,
I have a crosstab query that works OK when I manually
Click to expand...
run
it.
Here
is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending Date]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And
[Enter
Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record
source
to
a
report. First off, I get prompted
four time for the input fields instead of just two. Then I get
this
message
from Access 2002:

"The Microsoft Jet database engine does not recognize " as a valid
field
name or expression"

Again, if I run the query manually, it prompts me for two input
values
and
display the correct data, but when I
try to assign it to a new report, I get errors.

Any suggestions as to what I might be doing wrong or missing?

Thank you,
Thomas Kroljic
Click to expand...
Click to expand...
Click to expand...
 
Thomas,
Until you pointed out the lack of Query Parameters, I hadn't even noticed.
It was new information for me also. I didn't realize the parameters weren't
required if you use the Column Headings property (IN clause). Other queries
don't require specifying parameter data types probably because they have
less "work to do" in generating the record set. This is a WAG.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thomas Kroljic said:
Duane,
Based on your second comment below, I added the "in" syntax to the end
of my crosstab query with the proper codes and now the query with the report
works fine. THANK YOU!!!

I looked up the "IN" clause in some of my Access books, but they really
do not describe the importance or the effect that this clause can have. It
appears that the "IN" clause can help you control the column headings,
especially the display order of the columns.

So why does adding the "IN" clause to the crosstab query eliminate the
need to have a parameter definition?

Thanks for all your help.

Thomas J. Kroljic

Duane Hookom said:
1. Your code would make a combination of CallCodeID and Store. The append
query makes sure all possible combinations of these two for the date range
are included.
2. If you take out the Column Headings property value
"A","B","C","D","E","F","G","H", then you will get the error.

--
Duane Hookom
MS Access MVP


Thomas Kroljic said:
Duane,
I download your example database called CrossTab.mdb.
I noticed several things that are different:

1. the use of a initial query to rebuild the data table
(qappEmpCust) using the start and end date from your
frmReportSelect form.
2. In your query (qxtbEmpCustSales) I notice the use of the where
clause. What I found surprising was the absent of a parameter
Click to expand...
definition
via
Query/Parameter and no "parameter" syntax within the SQL statement itself.
Why doesn't this query need to define the parameters? If I remove
the parameter statement from my query I get an Microsoft error message "The
MS Jet database engine does not recognize
'[forms]![frmReportSelect]![txtstartdate]' as a valid field name or
expression.

I think I've had enough headaches with this query. Tomorrow I plan
on doing the following to accomplish my end results: first, I'll
Click to expand...
Click to expand...
execute
a start
and
Click to expand...
out
of And solution. by
Ken
Click to expand...
This
is
the
report I am using. It also contains some VBA code which I do understand.

I've copied (imported) his report from the CD that came
Click to expand...
with
the
book into my database. I added four additional labels, text, and sum
text
fields to the report. I then added my query see below:

PARAMETERS [forms]![myform]![beginningcalldate]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
totalcount
FROM Calls INNER JOIN Store ON Calls.StoreID =
Store.StoreId
***** WHERE
(((Calls.CallDate)>[forms]![myform]![beginningcalldate]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;


Duane, If I remove the where clause, the report runs great. If I include
the
where clause (and I added the parameter definition to the query),
Click to expand...
I
get
an
empty report. No data. If I use the debugger to follow the code
Click to expand...
this
is
what
I noticed. At the time the VBA code assign the record source to a new
rst
object (see code segment below) , when the code his the line
"intColCount
=
rst.Fields.Count", the value is 0. Again, this works perfectly if
Click to expand...
I
take
out
the where clause. At this point, I'm lost. I've search google looking
for
clues. I think I have my blinders on because I can not see what is
causing
this problem. Hopefully this data will help.

rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count

Thank you,
Thomas Kroljic


Printing a report based on queries may cause the report to "requery"
thus
asking for the same values again. That is one of the reasons
Click to expand...
Click to expand...
that
I
always
get values from controls on forms. This has always worked for me.

Did you set the column headings property?

--
Duane Hookom
MS Access MVP


Duane,
I've tried several times to use a form with controls the user
can
fill-in before running the report with the crosstab query, but
Click to expand...
I
get
an
error message stating the forms!myform!txt is not a field or
expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of
queries.
It's
when I open the report and try to run the report that I have
problems.

Even if I stay with parameters in the query, why would the system
prompt
me
double the amount of parameters that I've have listed. It's as if
during
each phase of the report opening up I get prompted for all
parameters.
What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

I would get the criteria from controls on a form rather than
parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static
set
of
CallCodeIDs then you should enter them into the Column Headings
property.

--
Duane Hookom
MS Access MVP


All,
I have a crosstab query that works OK when I manually run
it.
Here
is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending
Date]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID = Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting Date] And
[Enter
Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record
source
to
a
report. First off, I get prompted
four time for the input fields instead of just two. Then I get
this
message
from Access 2002:

"The Microsoft Jet database engine does not recognize " as a
valid
field
name or expression"

Again, if I run the query manually, it prompts me for two input
values
and
display the correct data, but when I
try to assign it to a new report, I get errors.

Any suggestions as to what I might be doing wrong or missing?

Thank you,
Thomas Kroljic
Click to expand...
Click to expand...
Click to expand...
 
Duane,
Thanks for all your time in responding back to my posting. Very helpful.
I'm glad we resolved the problem and learned something in the process.

Thomas J. Kroljic

Duane Hookom said:
Thomas,
Until you pointed out the lack of Query Parameters, I hadn't even noticed.
It was new information for me also. I didn't realize the parameters weren't
required if you use the Column Headings property (IN clause). Other queries
don't require specifying parameter data types probably because they have
less "work to do" in generating the record set. This is a WAG.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Thomas Kroljic said:
Duane,
Based on your second comment below, I added the "in" syntax to the end
of my crosstab query with the proper codes and now the query with the report
works fine. THANK YOU!!!

I looked up the "IN" clause in some of my Access books, but they really
do not describe the importance or the effect that this clause can have. It
appears that the "IN" clause can help you control the column headings,
especially the display order of the columns.

So why does adding the "IN" clause to the crosstab query eliminate the
need to have a parameter definition?

Thanks for all your help.

Thomas J. Kroljic

Duane Hookom said:
1. Your code would make a combination of CallCodeID and Store. The append
query makes sure all possible combinations of these two for the date range
are included.
2. If you take out the Column Headings property value
"A","B","C","D","E","F","G","H", then you will get the error.

--
Duane Hookom
MS Access MVP


Duane,
I download your example database called CrossTab.mdb.
I noticed several things that are different:

1. the use of a initial query to rebuild the data table
(qappEmpCust) using the start and end date from your
frmReportSelect form.
2. In your query (qxtbEmpCustSales) I notice the use of the where
clause. What I found surprising was the absent of a parameter definition
via
Query/Parameter and no "parameter" syntax within the SQL statement itself.
Why doesn't this query need to define the parameters? If I remove
the parameter statement from my query I get an Microsoft error message
"The
MS Jet database engine does not recognize
'[forms]![frmReportSelect]![txtstartdate]' as a valid field name or
expression.

I think I've had enough headaches with this query. Tomorrow
Click to expand...
I
plan
on doing the following to accomplish my end results: first, I'll
Click to expand...
execute
a
query that creates a table with the appropriate data based on the start
and
end dates from a user input form. Next, I'll create a general crosstab
query
against this table that will be used by my original report (the one
Click to expand...
out
of
the "Access 2000 Developers Handbook).

I still can't comprehend why your query doesn't need a parameter
statement and mine does. Any thoughts on this?

Again, thank you for all your help and input. I do appreciate
Click to expand...
it.
And
I
do eventually learn from my mistakes :)
And I do learn from examples such as CrossTab.mdb

Thank you,
Thomas J. Kroljic


No offense to Getz, Litwin, and Gilbert but I don't like their solution.
I
created and use the much more flexible (less code) solution for crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Duane,
First off, thanks for taking the time to help me out. I do
appreciate
it.

I have a book called "Access 2000 Developers handbook Vol 1" by
Ken
Getz, Paul Litwin and Mike Gilbert. This is my bible.

In chapter 9 there is an example of a crosstab report.
Click to expand...
This
is
the
report I am using. It also contains some VBA code which I do
understand.

I've copied (imported) his report from the CD that came with
the
book into my database. I added four additional labels, text, and sum
text
fields to the report. I then added my query see below:

PARAMETERS [forms]![myform]![beginningcalldate]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
totalcount
FROM Calls INNER JOIN Store ON Calls.StoreID =
Store.StoreId
***** WHERE
(((Calls.CallDate)>[forms]![myform]![beginningcalldate]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;


Duane, If I remove the where clause, the report runs great. If I
include
the
where clause (and I added the parameter definition to the
Click to expand...
Click to expand...
query),
I a
new
Click to expand...
if
I
Click to expand...
ActiveConnection:=CurrentProject.Connection,
_ that
Click to expand...
but
I
get
an
error message stating the forms!myform!txt is not a field or
expression.
I have the correct parameters defined in the query.

Again, all works fine when I open the query from the list of
queries.
It's
when I open the report and try to run the report that I have
problems.

Even if I stay with parameters in the query, why would the system
prompt
me
double the amount of parameters that I've have listed. It's
Click to expand...
as
if
during
each phase of the report opening up I get prompted for all
parameters.
What
would cause this?

Thanks for your input and help,
Thomas J. Kroljic

I would get the criteria from controls on a form rather than
parameter
prompts. ie:
Forms!frmYourForm!txtStartDate and Forms!frmYourForm!txtEndDate
Make sure you update your query parameters. If you have a static
set
of
CallCodeIDs then you should enter them into the Column Headings
property.

--
Duane Hookom
MS Access MVP


All,
I have a crosstab query that works OK when I
Click to expand...
manually
run
it.
Here
is
the code:

PARAMETERS [Enter Starting Date] DateTime, [Enter Ending
Date]
DateTime;
TRANSFORM Count(Calls.CallCodeId) AS CountOfCallCodeId
SELECT Store.StoreDesc, Count(Calls.CallCodeId) AS
TotalCalls
FROM Calls INNER JOIN Store ON Calls.StoreID =
Store.StoreId
WHERE (((Calls.CallDate) Between [Enter Starting
Click to expand...
Date]
And
[Enter
Ending
Date]))
GROUP BY Store.StoreDesc
ORDER BY Store.StoreDesc
PIVOT Calls.CallCodeId;

My problem come in when I assign this query as the record
source
to
a
report. First off, I get prompted
four time for the input fields instead of just two. Then
Click to expand...
I
get
this
message
from Access 2002:

"The Microsoft Jet database engine does not recognize "
Click to expand...
Click to expand...
as
a
Click to expand...
Click to expand...
 
Back
Top