Parameter query with check boxes for each month

M

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
 
D

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
 
M

mightymaggie

Hi Dale. Thanks so much for your response, I really appreciate it!

After I posted this I read some more on running queries from forms and
I think that's a good option for me. However, I'm not sure a RunMonth
column like you suggested would work as ads can run in more than one
month. A RunMonth column would definitely be easier and cleaner, but
I'm trying to avoid having my users enter in a new record for the same
ad each month. It makes sense, but it's time consuming for them. In
their old system they just click check boxes- it's horrible behind the
scenes in the database, but it's easy in the form and a quick visual.
Can I keep the check boxes (or something similar?) in the form and
still run a query where the user selects the month from a combo box to
run the monthly reports? Right now I see this happening by somehow
getting the choices in the combo box to map to my table of check
boxes... ie: choosing "January" in the combo box gets all the -1 values
in the January column. Sorry to post before I practice!

Thanks
Maggie
 
Top