Crosstab Query based on Query

  • Thread starter Thread starter Renee
  • Start date Start date
R

Renee

I created a query where it would prompt and ask for records between two
specific dates (i.e., Between [start date] And [end date].

Then I created a crosstab query based on that query, but it does not
recognize the date prompt in the first query.

How do I fix this? How do I get the crosstab query to pull records between
specific dates without having to write VBA (which I don't know how to write).
 
I created a query where it would prompt and ask for records between two
specific dates (i.e., Between [start date] And [end date].

Then I created a crosstab query based on that query, but it does not
recognize the date prompt in the first query.

How do I fix this? How do I get the crosstab query to pull records between
specific dates without having to write VBA (which I don't know how to write).
Click to expand...

It's always permissible (and usually a good idea) to explicitly specify your
parameters in a parameter query. With a Crosstab query it is obligatory!

Open the form in design view; right click the background of the table window
and select Parameters. Copy and paste the [start date] and [end date]
parameters - they must match EXACTLY - into the left column of the window and
select date/time as the parameter type in the right column. Or, open the query
in SQL view and edit

PARAMETERS [start date] DateTime, [end date] DateTime;

at the very top of the SQL window before the SELECT keyword.
 
I'm sorry, my fault. I need to explain a little more because I'm really
struggling with this. I am between a beginner and intermediate user, and
each time I work in Access, I learn a little bit more. So I hope you'll be
patient with me.

So I have a field in a query called "DateCompleted." And I'm trying to pull
up all the dates in that field between 06/01/2009 and 06/05/2009. The table
name the field "DateCompleted" is pulled from for this query is
"dbo_PR_Inspections".

In the criteria row for this I typed "Between [start date] and [end date]"
which works fine for a basic query, but when I create a crosstab query based
on this query, the prompt pop-up boxes do not work.

I tried as you suggested, and I still cannot get it to work. I get syntax
errors or I get all 220,000 records instead of the handful for that time
period.

I'm not very good with parameters, so I appreciate your help and patience.

BTW, I am on MS Office 2007 version of Access.

Thank you, again.

John W. Vinson said:
I created a query where it would prompt and ask for records between two
specific dates (i.e., Between [start date] And [end date].

Then I created a crosstab query based on that query, but it does not
recognize the date prompt in the first query.

How do I fix this? How do I get the crosstab query to pull records between
specific dates without having to write VBA (which I don't know how to write).
Click to expand...

It's always permissible (and usually a good idea) to explicitly specify your
parameters in a parameter query. With a Crosstab query it is obligatory!

Open the form in design view; right click the background of the table window
and select Parameters. Copy and paste the [start date] and [end date]
parameters - they must match EXACTLY - into the left column of the window and
select date/time as the parameter type in the right column. Or, open the query
in SQL view and edit

PARAMETERS [start date] DateTime, [end date] DateTime;

at the very top of the SQL window before the SELECT keyword.
Click to expand...
 
I'm sorry, my fault. I need to explain a little more because I'm really
struggling with this. I am between a beginner and intermediate user, and
each time I work in Access, I learn a little bit more. So I hope you'll be
patient with me.

So I have a field in a query called "DateCompleted." And I'm trying to pull
up all the dates in that field between 06/01/2009 and 06/05/2009. The table
name the field "DateCompleted" is pulled from for this query is
"dbo_PR_Inspections".

In the criteria row for this I typed "Between [start date] and [end date]"
which works fine for a basic query, but when I create a crosstab query based
on this query, the prompt pop-up boxes do not work.

I tried as you suggested, and I still cannot get it to work. I get syntax
errors or I get all 220,000 records instead of the handful for that time
period.

I'm not very good with parameters, so I appreciate your help and patience.

BTW, I am on MS Office 2007 version of Access.
Click to expand...

Please open your actual query (the one that's rejecting the parameters) in SQL
view and copy and paste the SQL to a message here. It'll probably be easier to
fix with a live example.
 
POST the SQL of the query that is NOT working. We should be able to
modify it or at least see what is wrong and make suggestions.

In Query design view, select View : SQL from the menu. Copy and paste
the SQL statement into your posting.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I'm sorry, my fault. I need to explain a little more because I'm really
struggling with this. I am between a beginner and intermediate user, and
each time I work in Access, I learn a little bit more. So I hope you'll be
patient with me.

So I have a field in a query called "DateCompleted." And I'm trying to pull
up all the dates in that field between 06/01/2009 and 06/05/2009. The table
name the field "DateCompleted" is pulled from for this query is
"dbo_PR_Inspections".

In the criteria row for this I typed "Between [start date] and [end date]"
which works fine for a basic query, but when I create a crosstab query based
on this query, the prompt pop-up boxes do not work.

I tried as you suggested, and I still cannot get it to work. I get syntax
errors or I get all 220,000 records instead of the handful for that time
period.

I'm not very good with parameters, so I appreciate your help and patience.

BTW, I am on MS Office 2007 version of Access.

Thank you, again.

John W. Vinson said:
I created a query where it would prompt and ask for records between two
specific dates (i.e., Between [start date] And [end date].

Then I created a crosstab query based on that query, but it does not
recognize the date prompt in the first query.

How do I fix this? How do I get the crosstab query to pull records between
specific dates without having to write VBA (which I don't know how to write).
Click to expand...
It's always permissible (and usually a good idea) to explicitly specify your
parameters in a parameter query. With a Crosstab query it is obligatory!

Open the form in design view; right click the background of the table window
and select Parameters. Copy and paste the [start date] and [end date]
parameters - they must match EXACTLY - into the left column of the window and
select date/time as the parameter type in the right column. Or, open the query
in SQL view and edit

PARAMETERS [start date] DateTime, [end date] DateTime;

at the very top of the SQL window before the SELECT keyword.
Click to expand...
Click to expand...
 
Back
Top