Run Query from From Suggestions

[spectraflame]

To simplify reporting, I would like to create a form where the user checks boxes and chooses dates that would in turn modify a query accordingly to generate a report.

I have the form created, but I am not sure where to go from there. How do I write a query that is controlled by the check boxes on this form? Are there better ways to achieve the same results without having to create a report for every possible report combination?

Suggestions and direction would be appreciated.

Matthew

 

[Cbrine]

I do this for a lot of my MSAccess work. It's not an easy process, but it is doable. I have a period select drop down that allows the user for select the time period, then a grouped set of radio buttons that allow a report selection. You need to setup code on the main form that will execute code based on the radio button selected. Then in your queries, place your cursor in the field where you want the form parameter to take effect. Right click and select build. Follow the tree view till you get to your form and select the date fields from the list. It will place a reference to that form control. When you execute the query with the form open, it will use the form value, otherwise it will prompt you for the value.

HTH.

 

[spectraflame]

WOW! Cbrine,

I will see what I can figure out. Sounds like this will be quite a challenge. I will post back if I get lost which I am sure I will.

Thanks,
Matthew

 

[spectraflame]

Cbrine,

Can you further explain what grouped radio buttons are and maybe point me to an example?

What I have in mind is as follows:

A combo box for Start Date and a separate one for End Date.
A check box or radio button for Status = Open
A check box or radio button for Status = Closed
A check box or radio button for Department - Electric
A check box or radio button for Department - Water
A check box or radio button for Department - Sewer
A check box or radio button for Department - Maintenance
A check box or radio button for Department - Locate
A check box or radio button for Department - Other

This way the user has the ability to generate a report for any or all departments, on any date range and choose the status (Open, Closed or Both).

Is this doable?



Matthew

 

[bat17]

Grouped buttons are radio buttons that are grouped together so that only one of them can be selected. So that if you wanted to give someone a choice of 4 reports then you could use the buttons so that they can only select one ata time.

if you look in help under radio buttons there is a fair bit of info.

Peter

 

[spectraflame]

I was able to create the option groups and think that being able to select only one choice will be OK.

My question is now, how do I pass the radio selections to the query to open the correct form with only the requested data?

I have a query created that includes all of the data that I need displayed and I have created the form for the data to be displayed upon. I just cannot understand how the selections from the reporting form like the following can be sent to the query to produce the correct form?

Status = Open
Department = Electric
Start Date = 08/31/04
End Date = 09/02/04

Maybe I am in over my head on this one?
Matthew

 

[bat17]

No, you are not over your head

I will have to make some assumptions though,
I will assume that, your form with all the buttons is called frmFilter, that the 2 dates are cboStartDate and cboEndDate.
that your status is stored in a yes/no field and that you have a radio group for it called fraStatus and the departments are listed in a radio group called fraDept.

Dates would be done by adding something like this to the criteria for the date field in your query
Between [Forms]![frmFilter]![cboStartDate] And [Forms]![frmFilter]![cboEndDate]

Status - as long as you set the group values to 0 and 1 then you just need
[Forms]![frmFilter]![fraStatus]

The departments are a little more fun
I assume that you all ready have a department table with ID numbers for the departments, if not just make one as a lookup table. Make sure that the values for the departments in the fraDept group match the ID's in the table.
Add this to your query so that you can filter on the ID numbers.
I would also add an option to the group for "All" and give it a value not used by any department, say "0"

Now in the ID criteria field you can add
Like IIf([Forms]![frmFilter]![fraDept]=0,"*",[Forms]![frmFilter]![fraDept])
This will return either a selected Department or all of them.

I hope that I have not confused you to0 much with this! If you get stuck I can knock up a small demo DB to show what I mean.

Peter

 

[spectraflame]

bat17,

An example would be appreciated. I have tried, but it is just not sinking in.

The information below may help you to make your example so that I may understand it better.

The form to select my report options is called "Reporting Menu"

For the start and end date, I am using an Active X Control MS Date Picker called ActiveXCtl20 & ActiveXCtl22 respectively. The Start and End Date fields in the main table are both set to Date/Time.

The radio option group for the Department is called "Fram49". I have 6 options in that option group: Electric, Water, Sewer, Maintenance, Locate, Other. The Department field in the main table is set to text.

The radio option group for the Status is called "Frame 64". I have 3 options in that option group: Open, Closed, Open/Closed or All. The Status field in the main table is set to text.

I created a Query called "Report Query". I have added all of the fields that I would like displayed on the report that I created called "Query Report".

Any information that you can pass along to help me better understand would be greatly apperciated.

Thanks again for your assistance.

Matthew