Crosstab query and report

[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

 

[Duane Hookom]

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.

 

[Thomas Kroljic]

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

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

 

[Duane Hookom]

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 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

 

[Marshall Barton]

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?

I thought that you had to explicitly declare the parameters
for a crosstab query?

 

[Thomas Kroljic]

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

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 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

 

[Thomas Kroljic]

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

 

[Marshall Barton]

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.

I don't use ADO so I won't be able add any value to this
discussion.

 

[Duane Hookom]

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. 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

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 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

 

[Thomas Kroljic]

Duane,
I'll download the file from the link you provide and review it.

Thanks,
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. 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

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

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

 

[Thomas Kroljic]

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

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. 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

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

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

 

[Duane Hookom]

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 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

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. 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


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 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

 

[Thomas Kroljic]

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

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 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

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

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


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 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

 

[Duane Hookom]

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

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

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 I plan
on doing the following to accomplish my end results: first, I'll

execute

a start
and

out

of And solution. by
Ken

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


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
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

 

[Thomas Kroljic]

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

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

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

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

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


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.

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 a
new

if

I

ActiveConnection:=CurrentProject.Connection,

_ that

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 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