Make reports based on selections 1

[rcreed1]

Hello, I needed to know if this is possible and how it could get done. I am new to access so it can not be answered with too much detail...
My question is, I have a database that stores a monthly report from 35 units (I am in the Army. So on the form to enter the report information I have added the following list boxes - Month, year, and unit so that I can make a query and show a units report for any particular month or year. However I want to create a form with a list box so you can select a unit (we will call them for this example A,B,C,D...) and then a month (JAN, FEB, MAR, APR...)and then a year (02, 03, 04, 05) and then a form or report is displayed with the information you requested.

I know how to make a query inputing that information but in order to make it user friendly and to avoid making over 300 possible queries I was hoping there was a way where the query is formed based on the users input to some questions.

Any help would be greatly appreciated, and if more than one
solution can be performed I would appreciate any possible procedures capable of acheiving this task. Thank you

 

[THWatson]

rcreed1
Here's one possible approach...there are others...

1. Create an unbound form. Let's call it frmSearch
2. Create an unbound text box in which you will enter the Year to choose. Let's call it txtYear (If you want the current year to show as the default, enter =Year(Date()) in the default value for that text box
3. Create an unbound list box for the month you wish to choose. Let's call it lstMonth. Base the list box on a Value List. The list will go like this...
"January";"1";"February";"2";"March";"3" and so on through "December";"12"
Show the column count as 2, Column widths as 1";0"

3. Create either a combo or list box for the Units. Let's call it cboUnits. Base it in a table/query, so that all your units will show in it.

4. Put a command button on your form, which will have the code to run the report you wish to run...something like
Dim stDocName As String
stDocName = "rptYourReportName"
DoCmd.OpenReport stDocName, acPreview
This report will have the query you are using as its record source.

5. Back in the query...
(a) In the Unit column, put this as criteria
Forms!frmSearch!cboUnits

(b) Assuming that you have a date field, create a new column MonthCheck: Format([Date Given],"mmmm"). In its criteria put
Forms!frmSearch!lstMonth

(c) Also create a column SortOrder: Int(Month([Date Given])) and set its sort to Ascending, to make your months sort in the proper order

(d) Create a column YearCheck: Year([Date Given])
As its criteria put
Forms!frmSearch!txtYear

That should give you a report for the Unit, Year and Month selected in the search form.

Hope that gives you a start.

Tom