Report grouped by months- cross tab 1

[MrsMope]

Good Afternoon,
I am completely dumbfounded, hopefully someone in her can give me a kick in the right direction.

Goal: Create a report showing quote totals for each month by sales person.
Problem: Users must be able to choose the date range they want
What I have done so far: I have two text boxes that allow date input in standard format dd/mm/yyyy, or a user can click the calendar control and choose the date.
I have a query called qryTestAverage with the following syntax:

SELECT Count(api_tblQuotes.QuoteID) AS NumberOfQuote, api_tblQuotes.QuoteDate, api_tblQuotes.[quote=Total], user_tblSalesmanProp.Description AS SalePerson, Month([QuoteDate]) AS MonthNum
FROM user_tblSalesmanProp INNER JOIN (user_tblAccountProp INNER JOIN api_tblQuotes ON user_tblAccountProp.ID = api_tblQuotes.Acct) ON user_tblSalesmanProp.ID = api_tblQuotes.Salesperson
GROUP BY api_tblQuotes.QuoteDate, api_tblQuotes.[quote=Total], user_tblSalesmanProp.Description, Month([QuoteDate]);

I then created a crosstab query to get my totals with the following syntax:

TRANSFORM Count(qryTestAverage.NumberOfQuote) AS CountOfNumberOfQuote
SELECT qryTestAverage.SalePerson, Count(qryTestAverage.NumberOfQuote) AS [Total Of NumberOfQuote]
FROM qryTestAverage
GROUP BY qryTestAverage.SalePerson
PIVOT Format([QuoteDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Now my dilema, how do I take the input from the textboxes and pass it using the docmd.openreport strwhere in a format that the crosstab will query correctly?

disregard thread703-1098482

 

[gol4]

how do I take the input from the textboxes and pass it using the docmd.openreport strwhere in a format that the crosstab will query correctly?

there is no easy way using openreport on a crosstab but to do what you want the easiest way would be, since you are basing the crosstab on a query just set the query that the crosstab is based on(qryTestAverage) criteria to the forms textboxes. I am pretty sure you know how to do that using the VBE and criteria set to forms!formname!fieldname.

 

[dhookom]

You will need to enter the Query->Parameter and then your control names and data types.

I would use only a starting or ending date with relative months. Check this out faq703-5466

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MrsMope]

Alright,
I think I understand what you are both saying, I changed the source query to read:

SELECT Count(api_tblQuotes.QuoteID) AS NumberOfQuote, api_tblQuotes.QuoteDate, api_tblQuotes.[quote=Total], user_tblSalesmanProp.Description AS SalePerson, Month([QuoteDate]) AS MonthNum
FROM user_tblSalesmanProp INNER JOIN (user_tblAccountProp INNER JOIN api_tblQuotes ON user_tblAccountProp.ID = api_tblQuotes.Acct) ON user_tblSalesmanProp.ID = api_tblQuotes.Salesperson
GROUP BY api_tblQuotes.QuoteDate, api_tblQuotes.[quote=Total], user_tblSalesmanProp.Description, Month([QuoteDate])
HAVING (((api_tblQuotes.QuoteDate)>[Forms]![frmReportListing]![txtToDate] And (api_tblQuotes.QuoteDate)<[Forms]![frmReportListing]![txtFromDate]));

Is that what both of you were suggesting?

 

[gol4]

Thats what I had in mind. Did it work for you?

 

[MrsMope]

yes sir, thank you very much.