Crosstab query question

  • Thread starter Thread starter RB
  • Start date Start date
R

RB

I have a database that keeps track of bids for jobs. I am
trying to create a chart in Access XP to show the number
of bids we issued for a given date range. I have a form
set up for the user to put in a start date and an end
date. The query calls for this data in the criteria row.
It works fine from the form to the query.

The problem is when I try to create the bar chart in a
report. It tells me that the expression in my underlying
query that calls for the date range is unknown or not
valid. The following fields are used in the query:

Issued - the date the bid was issued
Status - the status if the issued bid (Won, Lost, &c.)
Suffix - the bid number

The bar chart needs to show me how many bids are in what
status for each month during the user-inputted date range.
Here's the SQL code that is being used by the chart:

TRANSFORM Count(*) AS [Count]
SELECT (Format([Issued],"MMM 'YY"))
FROM [QuotationsIssuedQuery]
GROUP BY (Year([Issued])*12 + Month([Issued])-1),(Format
([Issued],"MMM 'YY"))
PIVOT [Status];

Is there a way to tell this TRANSFORM statement to limit
the date range to the user inputted date range (Between
Forms!DateRangeForm!StartDate and Forms!DateRangeForm!
EndDate)? Thanks for any help you can offer.

RB
 
I think all you have to do is select Query|Parameters and enter:
Forms!DateRangeForm!StartDate Date/Time
Forms!DateRangeForm!EndDate Date/Time
 
Thanks for the tip. It works. To anyone who uses this post
in the future, if Access flags you for an error for
invalid bracketing format, just go into SQL view on the
query, delete the brackets, say Yes to updating the data
source for the chart when you close the query, and that
problem is solved.

Thanks,
RB
-----Original Message-----
I think all you have to do is select Query|Parameters and enter:
Forms!DateRangeForm!StartDate Date/Time
Forms!DateRangeForm!EndDate Date/Time

--
Duane Hookom
MS Access MVP


I have a database that keeps track of bids for jobs. I am
trying to create a chart in Access XP to show the number
of bids we issued for a given date range. I have a form
set up for the user to put in a start date and an end
date. The query calls for this data in the criteria row.
It works fine from the form to the query.

The problem is when I try to create the bar chart in a
report. It tells me that the expression in my underlying
query that calls for the date range is unknown or not
valid. The following fields are used in the query:

Issued - the date the bid was issued
Status - the status if the issued bid (Won, Lost, &c.)
Suffix - the bid number

The bar chart needs to show me how many bids are in what
status for each month during the user-inputted date range.
Here's the SQL code that is being used by the chart:

TRANSFORM Count(*) AS [Count]
SELECT (Format([Issued],"MMM 'YY"))
FROM [QuotationsIssuedQuery]
GROUP BY (Year([Issued])*12 + Month([Issued])-1),(Format
([Issued],"MMM 'YY"))
PIVOT [Status];

Is there a way to tell this TRANSFORM statement to limit
the date range to the user inputted date range (Between
Forms!DateRangeForm!StartDate and Forms!DateRangeForm!
EndDate)? Thanks for any help you can offer.

RB
Click to expand...


.
Click to expand...
 
Back
Top