Parameter query with check boxes for each month

[mightymaggie]

I am redesigning my company's advertising database. I have a table
(tbl_Issue) containing 12 check boxes, 1 for each month. This is useful
in the form, as the salespeople want to see which months the ad is
running. Ads often run all 12 months.

I am trying to create a query that will return ads based on 2
parameters: publication and month. The publication part is easy, but I
can't figure out how to write a parameter for the check boxes. I want
the query to say "Which month" and when I type in "January", I want to
see all records with a check in the January check boxes.

Right now I have tbl_Issue.ID with 'Where' in the total row and
[January]=-1 in the criteria row. Then I have tbl_Issue.ID with 'Group
By' in the total row. This query returns all the January ads, but I'd
like to turn [January] into a prompt! As I have 4 publications running
ads each month, I really don't want to build a query for each. I tried
doing this without check boxes too- my queries are easy, but I don't
want to enter a new record in the form for each month.

Sorry this is long- thanks for any help!
Maggie

 

[Dale Fye]

Maggie,

Your best bet is to use a form to get the month the user wants to query on.
That way, you can use a combo box that contains the month names, and a
hidden field that contains the name of the field to select. This would be a
lot simpler if you had your database normalized so that you had a column
labeled RunMonth. Then you could just design the query to select the
correct month from this field. The way you have it setup, you will need to
write the SQL dynamically after they select the month, so you know which
field to put in the criteria section of your query.

To do this, you need to add a combo box to the form to select the
Publication, then a second combo to select the month. In this second combo,
use two columns in a value list, entering the field name that you want to
extract the data from in the first column (0) and the name of the month in
the second column. Then add a command button to the form (cmd_RunReport).
Lastly, design a report that uses the result of your query to display the
information you want. In the Click event of the command button, you will
need code that looks something like:

Private Sub cmd_RunReport_Click()

Dim strSQL as string

strSQL = "Select * FROM yourTable " _
& "WHERE [Publication] = " & chr$(34) & me.cbo_Publication &
chr$(34) _
& " AND [" & me.cbo_Month.column(0) & "] = -1"
currentdb.querydef("yourQuery").sql = strsql

Docmd.openreport ........

End

Hope this helps.

Dale