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.

 

[dhookom]

You might want to click the FAQs link above and scroll down to about the 10th entry for a possible solution.

I'm not sure why you think you need code. This solution would not require any.

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]

I have no date field. I have two fields Month and year so I'm not sure how to make you suggestion work. I've tried.

Thank you.

 

[dhookom]

If you have no date field, create one.
TheDateateSerial([YearField],[MonthField],1)
Your year and month fields are numeric aren't they?

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]

unfortunately not. That's the whole problem. The actual month is stored in the field (ie January, March, etc).

 

[dhookom]

You really aren't up a creek if you just try some stuff like:
TheDate: DateValue([MonthField] & " 1 " & [YearField])

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]

I'll give it a try and let you know how it goes.

Thank you for your help

Karen

 

[klrclark]

Where do I add this? Should I pass these values in from a form? I've never written a report that required this so pardon my lack of knowledge in this matter.

Thanks

Karen

 

[dhookom]

The solution in the FAQ requires a date field in a recordset (query/table). You can create a query based on your table with a calculated column as described in my most recent posting.

If you can't figure this out, come back with your table and field names.

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]

okay I have the query with the calculated date.

But where do I put this?

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)


Thank you for all of your help!

 

[dhookom]

Do you have a control on a form for the user to enter the ending date/month? If not, create one and come back with your form and control name as well as your query and field names.

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, I have a form with two combo boxes on it. One that has the months (cboMonthField) listed the other has year (cboYearField) in it.

Here is my query so far
TRANSFORM Sum(DataTable.Amount) AS [The Value]
SELECT DataTable.[Equipment Category], DataTable.Year, 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.Year, DataTable.[6053/CarWash]

ORDER BY DataTable.[Equipment Category]
PIVOT DateValue([Month] & " 1 " & [Year]);

Hope this isn't too confusing. Thank you!

 

[dhookom]

I would again expect a combo box or text box with a date value. Someone's choice to separate into character months and years really mucks things up when attempting to create functional reports.

Assuming:
- your form is named "YourForm"
- your month combo box has value like "January"
- your combo boxes pick the ending month

Try sql of:
TRANSFORM Sum(DataTable.Amount) AS [The Value]
SELECT DataTable.[Equipment Category], DataTable.Year, 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.Year, DataTable.[6053/CarWash]

ORDER BY DataTable.[Equipment Category]
PIVOT "M" & DateDiff("M",DateValue([Month] & " 1 " & [Year]), DateValue(Forms!YourForm!cboMonth & " 1 " & Forms!YourForm!cboYearField) ) IN ("M0","M1","M2","M3");

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]

would I call the query in my button to print the report? not bind it to the form. Right?

 

[dhookom]

You would use the query as the record source of your report. There would be no code except to open the report.

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]

Ok I have the code to open the form. Do I pass the values in the combo boxes in the Where statement?

 

[dhookom]

Your form with the combo boxes should be open with values selected in the combo boxes. Try to just view the datasheet of the crosstab query. Does this work?

If it works then try to open your report. Does this work?

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]

when I try to run the query it ask for the values in the cboMonthField and cboYearField? I have the form open with values selected.

 

[klrclark]

Sorry I had typed the wrong form name in. I am running it now.

Thank you for all of your help

 

[klrclark]

Do I need to add anything to the text boxes on my report for the months other than setting the control source to M0, M1, etc.?