Reports from a Crosstab Query

  • Thread starter Thread starter Tom Hall
  • Start date Start date
T

Tom Hall

When you click on a report designed from a crosstab
query, can you program it to ask for date parameters?
 
You can set up the crosstab query to ask for dates.

1. Drag the Date/Time field into the grid.

2. In the Total row beneath this field, choose:
Where

3. In the Criteria row, enter your parmeters, e.g.:
Between [StartDate] And [EndDate]

4. As always, it is a good idea to declare the parameters. Choose Parameters
on the Query menu, and in the dialog, enter:
StartDate Date/Time
EndDate Date/Time
 
I have tried that and I get an error message as
follows: "The Microsoft Jet database engine does not
-----Original Message-----
You can set up the crosstab query to ask for dates.

1. Drag the Date/Time field into the grid.

2. In the Total row beneath this field, choose:
Where

3. In the Criteria row, enter your parmeters, e.g.:
Between [StartDate] And [EndDate]

4. As always, it is a good idea to declare the parameters. Choose Parameters
on the Query menu, and in the dialog, enter:
StartDate Date/Time
EndDate Date/Time

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

When you click on a report designed from a crosstab
query, can you program it to ask for date parameters?
Click to expand...


.
Click to expand...
 
Does it actually say "<name>"?

Post the SQL statement (by switching the query to SQL View (on the View
menu)).

This is not a SQL statement being used by OpenRecordset in code, is it?
That's a different context.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have tried that and I get an error message as
follows: "The Microsoft Jet database engine does not
-----Original Message-----
You can set up the crosstab query to ask for dates.

1. Drag the Date/Time field into the grid.

2. In the Total row beneath this field, choose:
Where

3. In the Criteria row, enter your parmeters, e.g.:
Between [StartDate] And [EndDate]

4. As always, it is a good idea to declare the parameters. Choose Parameters
on the Query menu, and in the dialog, enter:
StartDate Date/Time
EndDate Date/Time


When you click on a report designed from a crosstab
query, can you program it to ask for date parameters?
Click to expand...


.
Click to expand...
Click to expand...
 
It does not recognize "Start Date" as a valid field name
or expression. The SQL view is as follows:

TRANSFORM Sum([Total Gate Count Query].Total) AS
SumOfTotal
SELECT [Total Gate Count Query].Month, [Total Gate Count
Query].[Week No], Sum([Total Gate Count Query].Total) AS
[Weekly Total]
FROM [Total Gate Count Query]
WHERE ((([Total Gate Count Query].Date) Between
[StartDate] And [EndDate]))
GROUP BY [Total Gate Count Query].Month, [Total Gate
Count Query].[Week No]
PIVOT [Total Gate Count Query].Day;
-----Original Message-----
Does it actually say "<name>"?

Post the SQL statement (by switching the query to SQL View (on the View
menu)).

This is not a SQL statement being used by OpenRecordset in code, is it?
That's a different context.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have tried that and I get an error message as
follows: "The Microsoft Jet database engine does not
-----Original Message-----
You can set up the crosstab query to ask for dates.

1. Drag the Date/Time field into the grid.

2. In the Total row beneath this field, choose:
Where

3. In the Criteria row, enter your parmeters, e.g.:
Between [StartDate] And [EndDate]

4. As always, it is a good idea to declare the parameters. Choose Parameters
on the Query menu, and in the dialog, enter:
StartDate Date/Time
EndDate Date/Time


When you click on a report designed from a crosstab
query, can you program it to ask for date parameters?


.
Click to expand...
Click to expand...


.
Click to expand...
 
Go back to #4 of Allen's first reply.

--
Duane Hookom
MS Access MVP
--

It does not recognize "Start Date" as a valid field name
or expression. The SQL view is as follows:

TRANSFORM Sum([Total Gate Count Query].Total) AS
SumOfTotal
SELECT [Total Gate Count Query].Month, [Total Gate Count
Query].[Week No], Sum([Total Gate Count Query].Total) AS
[Weekly Total]
FROM [Total Gate Count Query]
WHERE ((([Total Gate Count Query].Date) Between
[StartDate] And [EndDate]))
GROUP BY [Total Gate Count Query].Month, [Total Gate
Count Query].[Week No]
PIVOT [Total Gate Count Query].Day;
-----Original Message-----
Does it actually say "<name>"?

Post the SQL statement (by switching the query to SQL View (on the View
menu)).

This is not a SQL statement being used by OpenRecordset in code, is it?
That's a different context.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have tried that and I get an error message as
follows: "The Microsoft Jet database engine does not
recognize <name> as a valid field name or expression.
(Error 3070)". Is there something I am doing wrong?

-----Original Message-----
You can set up the crosstab query to ask for dates.

1. Drag the Date/Time field into the grid.

2. In the Total row beneath this field, choose:
Where

3. In the Criteria row, enter your parmeters, e.g.:
Between [StartDate] And [EndDate]

4. As always, it is a good idea to declare the
parameters. Choose Parameters
on the Query menu, and in the dialog, enter:
StartDate Date/Time
EndDate Date/Time


in message
When you click on a report designed from a crosstab
query, can you program it to ask for date parameters?


.
Click to expand...


.
Click to expand...
Click to expand...
 
I do not understand how to do it.
-----Original Message-----
Go back to #4 of Allen's first reply.

--
Duane Hookom
MS Access MVP
--

It does not recognize "Start Date" as a valid field name
or expression. The SQL view is as follows:

TRANSFORM Sum([Total Gate Count Query].Total) AS
SumOfTotal
SELECT [Total Gate Count Query].Month, [Total Gate Count
Query].[Week No], Sum([Total Gate Count Query].Total) AS
[Weekly Total]
FROM [Total Gate Count Query]
WHERE ((([Total Gate Count Query].Date) Between
[StartDate] And [EndDate]))
GROUP BY [Total Gate Count Query].Month, [Total Gate
Count Query].[Week No]
PIVOT [Total Gate Count Query].Day;
-----Original Message-----
Does it actually say "<name>"?

Post the SQL statement (by switching the query to SQL View (on the View
menu)).

This is not a SQL statement being used by
Click to expand...
OpenRecordset
in code, is it?
That's a different context.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have tried that and I get an error message as
follows: "The Microsoft Jet database engine does not
recognize <name> as a valid field name or expression.
(Error 3070)". Is there something I am doing wrong?

-----Original Message-----
You can set up the crosstab query to ask for dates.

1. Drag the Date/Time field into the grid.

2. In the Total row beneath this field, choose:
Where

3. In the Criteria row, enter your parmeters, e.g.:
Between [StartDate] And [EndDate]

4. As always, it is a good idea to declare the
parameters. Choose Parameters
on the Query menu, and in the dialog, enter:
StartDate Date/Time
EndDate Date/Time


in message
When you click on a report designed from a crosstab
query, can you program it to ask for date parameters?


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
While the query is in design view, select menus Query|Parameters and enter
your exact parameters and data types.

--
Duane Hookom
MS Access MVP


Tom Hall said:
I do not understand how to do it.
-----Original Message-----
Go back to #4 of Allen's first reply.

--
Duane Hookom
MS Access MVP
--

It does not recognize "Start Date" as a valid field name
or expression. The SQL view is as follows:

TRANSFORM Sum([Total Gate Count Query].Total) AS
SumOfTotal
SELECT [Total Gate Count Query].Month, [Total Gate Count
Query].[Week No], Sum([Total Gate Count Query].Total) AS
[Weekly Total]
FROM [Total Gate Count Query]
WHERE ((([Total Gate Count Query].Date) Between
[StartDate] And [EndDate]))
GROUP BY [Total Gate Count Query].Month, [Total Gate
Count Query].[Week No]
PIVOT [Total Gate Count Query].Day;

-----Original Message-----
Does it actually say "<name>"?

Post the SQL statement (by switching the query to SQL
View (on the View
menu)).

This is not a SQL statement being used by OpenRecordset
in code, is it?
That's a different context.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have tried that and I get an error message as
follows: "The Microsoft Jet database engine does not
recognize <name> as a valid field name or expression.
(Error 3070)". Is there something I am doing wrong?

-----Original Message-----
You can set up the crosstab query to ask for dates.

1. Drag the Date/Time field into the grid.

2. In the Total row beneath this field, choose:
Where

3. In the Criteria row, enter your parmeters, e.g.:
Between [StartDate] And [EndDate]

4. As always, it is a good idea to declare the
parameters. Choose Parameters
on the Query menu, and in the dialog, enter:
StartDate Date/Time
EndDate Date/Time


in message
When you click on a report designed from a crosstab
query, can you program it to ask for date parameters?


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top