Multiple inputs on a form to filter a query

[mhankins]

So I'm building a database that allows users to make entries into the records tables and generate reports based on those records.

The db has 3 tables (buildings, records, services), one query, and one report. If you open it and hit Generate Report on the switchboard you're going to get the options form which we want to use to build our query.

The trick is, if we left all options blank we would want every record. If we set one, we want to filter by it. So on and so forth.

If you can help me or point me in the right direction, thanks for your time.

 

[pbaldy]

Two options come to mind. For a very limited number of options (maybe 3 tops) I'd use this:

http://access.mvps.org/access/queries/qry0001.htm

I often use the dynamic SQL method demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

In your case, perhaps building the SQL and passing it to the report being opened in OpenArgs.

 

[mhankins]

I really like the dynamic sql idea and appreciate the insight. I'm going to try to pull it off. I'm a bit new for such a tactic though.

 

[pbaldy]

No problem. Dive in and if you get stuck post back and we'll sort it out.