Need to create a selective(filte red) 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]

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.