Need to create a selective(filtered) lookup report (thingy?)

[Daedalus]

Ok I'm once again showing my complete (or almost) ineptitude with all things Access.

I'm using Access 2007 and need to do the following:

SETUP:

I have a table of membership information, and a table of dues information. They are linked via a unique ID number so that I can see the data for each entry without opening the corresponding table.

PROBLEM:

I need to create a form (or whatever I need to accomplish my goal) that basically allows me to select the filter parameters by clicking.

Like so maybe:

A form that uses a check box for each month. By selecting the months you want to see and then hitting a "Generate" button. It then generates a report of dues paid in those months, and displays them alongside a number and a name from the other table.

I have no idea how to link each check box to a field in a table and then getting all those selected to be joined in a report.

I'm not even sure it can be done.

Can anyone guide a hapless, gormless, ignorant rookie trying????

Please, with sugar on top?

[NeoPa]

Let's start with the concepts first then. We can sugar coat if you stick with it.

1. You need a report to display the information. Don't worry at this stage about the filtering. Design a report that can show the whole dataset.
2. You need a form to select your criteria from. On the form you need to put whatever controls are required to allow the operator to choose the criteria for selecting only the item(s) they want.
3. When they have selected the selections, a Command Button control should be clicked to trigger your code to run the report.
4. Your code needs to set up a filter string, the complexity of which is determined by your form.
5. Lastly, your code needs to invoke the report, but should include passing the filter string in the invocation.

The effect of this will be that the report (which can show your whole dataset) will just show the filtered data - as you want it to.

[Daedalus]

Damn I was hoping to avoid using codes and I can't write that stuff.

Ok I now have a form that contains twelve check boxes and two command buttons. One button to open the report for the whole year and one that will operate the filter command.

I also have the report created to show the whole year.

[NeoPa]

You'd be surprised what you can manage with a little help ;)

First of all, we need to know what your CheckBoxes are supposed to do. You can start with just a couple of them to give a general idea.

Now look in Example Filtering on a Form to see the concepts explained and even reproduce the example to get a good feel of this subject. When you've done that, come back and we can take you through it as it relates to your situation (if you even need any more help at that stage).

[Daedalus]

I haven't worked with VB code in over 10 years and that was an amateur effort. So help will definently be needed on that.

The checkboxes are all labeled "January, Febuary..." and so forth - one for each month

I also have one command button labeled "Show_Dues_Report" to open the report (Called "Dues_Report") showing the whole year.

Lastly I have one more command button labelled "Create_Report" intended to be used for the filtering code to only display those months that have been checked off in the Check Boxes.

[NeoPa]

First of all you don't need to have two separate buttons to open the report.
One button can have code that checks the CheckBoxes and only adds the filter if any are checked (True).

Now, as in my earlier post (#4), I need you to look at the tutorial. This may sound harsh, but I don't want to work with you if you haven't tried it out, as that will be more work for me. If you find that you can't get it to work, let me know. I can work with that. What I can't (read am not prepared to) do is work with people who expect me to do all the donkey work for them. Not that I'm trying to suggest this is you. I'm simply redirecting you to that important bit of the previous post ;)

[Daedalus]

No problem with that, just wanted to stress the point that I am much more familair with Access thatn I am with writing anyything in code.

And that tutorial will be getting some me-time with me shortly.

Nice to know I only need the one button to operate the whole thing.

BUT since the form is set up so that all the code is in one command button, will there be diffucties in tying in the checkboxes to the code, or can it be made to see if functions outside it own object are triggered?

[NeoPa]

...
BUT since the form is set up so that all the code is in one command button, will there be difficulties in tieing in the checkboxes to the code, or can it be made to see if functions outside its own object are triggered?

No. The code behind the button can refer quite easily to all the controls on the form. Checkboxes are particularly easy as you just say :

If [CheckBoxName] Then ...

to see if it's checked.

[Daedalus]

Ok I've been over the tutorial a few times, I think that I'll have to do it again and again cause that was hard reading for me right now.

But I figure that the structure of the code should be along these lines:

IF [Checkbox:January] Then print Table[Dues/Column January] to Report[Dues_Overview]
(obviously not correct formatted code, have no clue yet as to what that should be)
Repeated for other months

And I think I don't need anything special to print the whole thing as I think the structure of the VB code can be set up so that the filter is only activated when one or more checkboxes are used.

If not then I might simply need to start the code with another set of IF [Chechbox] commands to launch the filter code...

Am I on the right track with this...?

[NeoPa]

Ok I've been over the tutorial a few times, I think that I'll have to do it again and again cause that was hard reading for me right now.

But I figure that the structure of the code should be along these lines:

IF [Checkbox:January] Then print Table[Dues/Column January] to Report[Dues_Overview]
(obviously not correct formatted code, have no clue yet as to what that should be)
Repeated for other months

And I think I don't need anything special to print the whole thing as I think the structure of the VB code can be set up so that the filter is only activated when one or more checkboxes are used.

If not then I might simply need to start the code with another set of IF [Chechbox] commands to launch the filter code...

Am I on the right track with this...?

Well, yes and no really.
Yes you do need to check the [CheckBox]es in your code.
No you would not do it in the way you're talking about.

Here's a very simple (as simple as I can make it but my explanations are sometimes considered obscure) explanation of the concepts.

• When running a report, there is an option to Filter the results. IE. A report designed to show everything can be used, without changing the design of it in any way, to show selected items. This is done simply and easily by passing a Filter string when opening it.
• What you need to do (and we can certainly help you here) is to create a string of SQL Filter words from the information contained on the form. The SQL string will contain the whole of the selection requirement before being passed to the DoCmd.OpenReport function.
• You can build up the string, one item at a time, in your VBA code connected to the CommandButton that is clicked to run the report.
• The resultant string is simply a list of checks which can be joined logically with AND and/or OR keywords. These checks are to be applied to each record as it is processed to determine whether it should be included in the results (report).

[NeoPa]

Please be sure to read these recent posts in order. It would have been a bit of a chunk to read and get to grips with all at once.

A problem many people struggle with when applying filter strings is "How to apply the AND and OR keywords?"
Seems obvious, but if I said we wanted to see records that were from January AND February, but no other months, would you use AND or OR in the Filter string?
If you said AND then you'd be wrong.
The Filter is checking each record as it is processed. Can any record be from both January AND February? No, it can't (You'd have no results at all). For each record, you want to determine if it is either January OR February (even though you want to include records from January AND February in your results set).

[NeoPa]

Please be sure to read these recent posts in order. It would have been a bit of a chunk to read and get to grips with all at once.

Now I need to make some assumptions as you haven't shared any information about what you have in your database. I will assume a simple table :
Table Name=tblDue

Field; Type; IndexInfo
CustomerID; Autonumber; PK
PayDate; Date/Time
Amount; Number (Currency)

The [tblDue] table (in this assumed (and simple) scenario) is the RecordSource for the [rptDues] report. The form ([frmDues]) has twelve CheckBoxes named [chkJan] through [chkDec] and a CommandButton [cmdReport].

Private Sub cmdReport_Click()
  Dim strFilter As String
 
  strFilter = ""
  If Me.chkJan Then strFilter = strFilter & ",1"
  If Me.chkFeb Then strFilter = strFilter & ",2"
  If Me.chkMar Then strFilter = strFilter & ",3"
.
.
  If Me.chkDec Then strFilter = strFilter & ",12"
  If strFilter > "" Then
    strFilter = "Month([PayDate] In(" & Mid(strFilter, 2) & ")"
    Call DoCmd.OpenReport(rptDues, acViewPreview, ,strFilter)
  Else
    Call DoCmd.OpenReport(rptDues, acViewPreview)
  End If
End Sub

I chose to use the more appropriate In() construct for the Filter here as it fits in more neatly. It would be possible to create a string which had a bunch of clauses ORed together like :

(Month([PayDate])=1) OR (Month([PayDate])=3)

For a report showing January AND March Dues.

Please let me know how you get on with all this. If you do have problems, please try to explain exactly where, relative to what I was saying.

[RZ15]

I have a question regarding this.

NeoPa, what you have outlined, that results in a total amount due for the customer in whatever months selected, correct? Is there any way to show the months as fields and have amount due for each month shown if the query the report is based on used has a field for each corresponding month?

So if the user checked January and February, they will see the fields January and February and none of the other months.

[NeoPa]

I'm sorry RZ, but your post (though it may seem connected) is off topic. You will need to post your own (separate) question about this. You may include a link to this one if you think that may help to explain it better, or even (in this case) a link from here to your new thread. That way experts involved in this thread may get a hook in. It also gives the opportunity for other experts to get involved. What you're asking is actually a lot more complicated and un-database like than maybe you realise.
Good luck with your question.