Determine what query to run based on a checkbox

[dwheatley]

Hi,

I have a form which I use to select a company and a service, and then click a button 'Run Query', returning results according to what company or service were selected.

Currently these results are automatically date-filtered to only show results with publication deadlines in the future. These deadlines are drawn from one table, tblDeadlines, and the Company/Service data drawn from tblServicePrice (see SQL below).

I want to instead have a checkbox on the form, which will apply this date filter when I click the 'Run Query' button only if it is ticked. How should I go about this?

Here is the current SQL for my Query:




SELECT tblCompanies.Company, tblServicePrice.Service, tblServicePrice.Price, tblServicePrice.Currency, tblServicePrice.[Number of Issues], tblServicePrice.Comments, tblDeadlines.Deadline
FROM (tblCompanies INNER JOIN tblServicePrice ON tblCompanies.ID = tblServicePrice.Company) INNER JOIN tblDeadlines ON tblCompanies.ID = tblDeadlines.Company
WHERE (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company])) OR (((tblDeadlines.Deadline)>Date()) AND (([Forms]![SearchF]![Service]) Is Null) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblServicePrice.Service)=[Forms]![SearchF]![Service]) AND ((tblDeadlines.Deadline)>Date()) AND (([Forms]![SearchF]![Company]) Is Null)) OR (((tblDeadlines.Deadline)>Date()) AND ((tblServicePrice.Company)=[Forms]![SearchF]![Company]) AND (([Forms]![SearchF]![Service]) Is Null));

Thanks!

[ranman256]

Checkboxes constantly have to be programmed (when modified)
instead what about a combo box?
the query in the box has the 'caption' and query to run
[caption],[qry]
All Clients, qsClientsAll
Delinquent, qsClientsDelinq
Current clients, qsClientsCurr


The user sees the caption, but runs the query when picked.
bound col = 2
set col widths = 1;0 (set zero so the user cant see the query)

then in the combo AFTERUPDATE event, filter or run the query.
Just add new queries to the table and the combo is self programming, unlike checkboxes.

[orange]

See this material from Martin Green, and see if gives you some ides/options.