Report with Query generated in code behind it

[klrclark]

I have a report that the user needs to enter the month they would like to start at (ie January) In the code it will also pull the 2 months before that month. the user is then asked what year they would like to view. (ie 05). If the month is January or February then the year they enter has to subtract 1 so it will pull the other past months from the right year.

After all that they want the report to be formatted like this:

Equipment Category
Month1, Month2, Month3
Total for Month1,Total for Month2,Total for Month3

I've tried doing a crosstab but it doesn't pull the right months and year.

Any help would be greatly appreciated.

 

[klrclark]

Am I not suppose to put anything in the parameter clause of the query? I have tried to follow the FAQ example but everytime I enter the parameter DateValue([Forms]![3 Month Summary Report Form]![cboMonth] & " 1 " & [Forms]![3 Month Summary Report Form]![cboYearField]) I get an error. But I don't know what else to enter or how to format it so access likes it.

thanks.

 

[dhookom]

Is the form open?
What are the possible values in the combo boxes?
When the form is open, press Ctrl+G to open the debug window and enter:

?[Forms]![3 Month Summary Report Form]![cboMonth]
press enter
?[Forms]![3 Month Summary Report Form]![cboYearField]
press enter

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]

 

[klrclark]

Yes

January, February, March, etc in the cboMonthField
1 through 9 in the cboYearField

I did the debug window and this is what it showed

?[Forms]![3 Month Summary Report Form]![cboMonthField]
March
?[Forms]![3 Month Summary Report Form]![cboYearField]
2

 

[klrclark]

I think I am missing something in my query

TRANSFORM Sum(DataTable.Amount) AS [The Value]
SELECT DataTable.[Equipment Category], Sum(DataTable.Amount) AS [Total Of Amount]

FROM DataTable

WHERE (((DataTable.[Store Number]) Not In (850,851,890,0,891)) AND ((DataTable.[GL #]) In ("6050","6051","6053"))) OR (((DataTable.[Store Number]) Not In (850,851,890,0,891)) AND ((DataTable.[6053/CarWash])=Yes))

GROUP BY DataTable.[Equipment Category], DataTable.[6053/CarWash]

PIVOT "M" & DateDiff("m",DateValue([Month] & " 1 " & [Year]),DateValue(Forms![3 Month Summary Report Form]!cboMonthField & " 1 " & Forms![3 Month Summary Report Form]!cboYearField)) In ("M0","M1","M2");

I have the columns printing on the report. If the user selects March in the combo box on the form then it displays January, February, March

but I'm not limiting my recordset. I am getting data I don't need from other years.

thanks again for all of your help.

Karen

 

[dhookom]

Think about this:
[cboMonthField]="March"

[cboYearField] = 2

What would you expect to return as a date value from:
DateValue([cboMonthField] & " 1 " & [cboYearField])

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]

 

[klrclark]

1/1/2005,2/1/2005, and 3/1/2005 but I would have to convert them to where
[Month]IN(January,February,March) and [Year] =5

but if the user selected January or March then my year would change (ie December would be 4 and then January and February would be 5)

I've been working on it and I have the month printing at the top but I am having trouble with the criteria part to make sure that it looks and only pulls the month and year I want.


thank you very much for your help.

 

[dhookom]

How are you using cboYearField? Is it to identify the year of the date? If so, 5 would be about 2000 years ago. What are you storing in your year field in your table? Are you also storing 5 rather than 2005?

If you were working purely with dates (which I have been attempting to create based on your fields and combo boxes) then subtracting 3 months from a February date would correctly grab a month/date from the previous year.

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]

 

[klrclark]

5 is stored in the field in the table. I know it is very stupid, but we inherited the database from a user.

Would it be easier just to create a field in the table and insert a date - like if it was December(Month) and 4(Year) then I would insert 12/1/2004 in the new field.

Then you example would work - right?

 

[dhookom]

Try:

TRANSFORM Sum(DataTable.Amount) AS [The Value]
SELECT DataTable.[Equipment Category], Sum(DataTable.Amount) AS [Total Of Amount]

FROM DataTable

WHERE (((DataTable.[Store Number]) Not In (850,851,890,0,891)) AND ((DataTable.[GL #]) In ("6050","6051","6053"))) OR (((DataTable.[Store Number]) Not In (850,851,890,0,891)) AND ((DataTable.[6053/CarWash])=Yes))

GROUP BY DataTable.[Equipment Category], DataTable.[6053/CarWash]

PIVOT "M" & DateDiff("m",DateValue([Month] & " 1 " & 2000+[Year]),DateValue(Forms![3 Month Summary Report Form]!cboMonthField & " 1 " & 2000+Forms![3 Month Summary Report Form]!cboYearField)) In ("M0","M1","M2");


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]

 

[klrclark]

That works.