Selecting Months on a Report

[Norstar2]

I have been digging and digging and haven't had any luck.

I am creating a report on the fly to show sales per month, but I do not want to show every month. I only wanted to show the months that I select from checkboxes to show up on the report. I'm not exactly sure how to setup up the report to query to take in the data from the checkboxes and include the month's headers and sales information under it

Any help would be greatly appreciated!

 

[jahaines82]

If you want to filter a report via form controls then you can use the format [Forms]![YouForm]![Form Control] in the criteria section of you query.

 

[Norstar2]

I'm having a little trouble implementing this. I add all 12 months to the query and filter using the contols, but my database hangs most of the time. I think maybe my query is too complex. Is there an easier way?

 

[Norstar2]

What I really want to do is to create a query on the fly. Instead of manually putting in the months, I want to have a form to take input (month as a header, and days of the months as a row for the report) and create the query based off of that input.

 

[ThreeCrow]

Give me some details about your Month Selection Form

How do your CheckBoxes relate to the Month, are you going to want to use just the current year, or would want to select are year along with the Month.

Do you have any of setup yet?

 

[Rai]

Solution with code

on my selection form i have tick boxes all neame by the name of the month so the tick box for january is called January ect....

anyways the field in the report is called month were it looks for the month value which was selected from a dropdown box but could be typed in.

Also i have these reports stretching over multiple years so i added a year field.

here is the code.

Dim ReportAns As String

DoCmd.SetWarnings Off

If January = True Then
ReportAns = "Month = 'January'"
End If

If February = True Then
If ReportAns = "" Then
ReportAns = "Month = 'February'"
Else
ReportAns = ReportAns + " or Month = 'February'"
End If
End If

If March = True Then
If ReportAns = "" Then
ReportAns = "Month = 'March'"
Else
ReportAns = ReportAns + " or Month = 'March'"
End If
End If

If April = True Then
If ReportAns = "" Then
ReportAns = "Month = 'April'"
Else
ReportAns = ReportAns + " or Month = 'April'"
End If
End If

If May = True Then
If ReportAns = "" Then
ReportAns = "Month = 'May'"
Else
ReportAns = ReportAns + " or Month = 'May'"
End If
End If

If June = True Then
If ReportAns = "" Then
ReportAns = "Month = 'June'"
Else
ReportAns = ReportAns + " or Month = 'June'"
End If
End If

If July = True Then
If ReportAns = "" Then
ReportAns = "Month = 'July'"
Else
ReportAns = ReportAns + " or Month = 'July'"
End If
End If

If August = True Then
If ReportAns = "" Then
ReportAns = "Month = 'August'"
Else
ReportAns = ReportAns + " or Month = 'August'"
End If
End If

If September = True Then
If ReportAns = "" Then
ReportAns = "Month = 'September'"
Else
ReportAns = ReportAns + " or Month = 'September'"
End If
End If

If October = True Then
If ReportAns = "" Then
ReportAns = "Month = 'October'"
Else
ReportAns = ReportAns + " or Month = 'October'"
End If
End If

If November = True Then
If ReportAns = "" Then
ReportAns = "Month = 'November'"
Else
ReportAns = ReportAns + " or Month = 'November'"
End If
End If

If December = True Then
If ReportAns = "" Then
ReportAns = "Month = 'December'"
Else
ReportAns = ReportAns + " or Month = 'December'"
End If
End If

DoCmd.OpenReport "Stats And Productivity Information", acViewPreview, , "Year = '" & YearSel & "'" & " and " & ReportAns, acWindowNormal

End Sub

PM me for any more help of post it on here ure choice.
Thanks

 

[Rai]

Sorry if you like i will send you the forms and the reports for you to look at if you like.

 

[ThreeCrow]

Have a method I think will work for your.

Will need to see your Form and Report. Its a little detailed.

attach a stripped down of your dataBase zipped (100k or under) or private email.

 

[Rai]

mine works i was advising NorStar2 of the code i used. it works perfectly.

 

[Norstar2]

Sure, I would like to see the forms/reports you have Rai. You can either attach them to the board or I can PM your my email.

I really appreciate it.

 

[Norstar2]

Here is my database. The main form is Switchboard...and the form I am working on to print the reports is PrintReport. An example of criteria for my serach is...

List the Sales by Joseph for April, May, and August between the amounts of 25 and 200.

I have the form...I am having difficulty implementing the code behind it.

 

[Rai]

Hi, Do you mind i i totally redeignt your table for ease mainly.
you should like the outcome?

 

[Norstar2]

I really need to keep the 12 tables seperate.

But I would like to see what you had in mind.

 

[Norstar2]

Here is an example SQL Query that I would have to run.

SELECT Seller.SellerID, February.Amount, February.Date, July.Date, March.Date
FROM Seller INNER JOIN ((February INNER JOIN July ON February.Amount = July.Amount) INNER JOIN March ON February.Amount = March.Amount) ON (Seller.SellerID = March.SellerID) AND (Seller.SellerID = July.SellerID) AND (Seller.SellerID = February.SellerID)
WHERE (((Seller.SellerID)=1) AND ((February.Amount)=14));



This Query Select and dispalys the Dates that SellerID 2(Joseph) sold the amount of $14.

I need my checkboxes to generate this kind of query.

 

[Norstar2]

I re-designed the database to just use 1 table for all the test results. I would like to see the way you implemented your solution Rai..your forms and reports that is.

 

[raskew]

Hi -

It doesn't need to be this difficult. Take the Orders table from Northwind. Say you wanted to return all Orders where:
- Freight >= 25
- The OrderDate month was an even numbered month (e.g., Feb (2), Apr (4), etc.) from the years 1994 and 1995:

SELECT
    Orders.OrderID
  , Orders.CustomerID
  , Orders.EmployeeID
  , Orders.OrderDate
  , Orders.Freight
FROM
   Orders
WHERE
   (((Orders.Freight)>=25) 
AND
   ((Month([OrderDate])) In (2,4,6,8,10,12)) 
AND
   ((Year([OrderDate])) In (1994,1995)))
ORDER BY
   Orders.OrderDate;

Give it a try. It's a working query that you can copy/paste into a new query in Northwind and modify to your heart's content.

Granted it doesn't immediately answer the form / report / checkbox questions but hopefully does show how to return a recordset without multiple Iif() statements.

HTH - Bob