I'm sure I'm not the first to ask this:

I have a query that is based on two tables: tblMeasures and tblMeasureSetJunction. The latter is a junction table between tblMeasures and a third table that that has a many to many relationship with tblMeasues. There is a form, frmAddMeasure, that uses tblMeasures as its recordsource and has a subtable that uses tblMeasureSetJunction as a recordsource.

By programatically changing a few properties of the form, I can use the same form to allow the user to either add, edit, or review the information in the database.

I have just spent many hours (days actually, since I'm new at this and teaching myself as I go) developing another form frmSearchMeasures, that allows a user to specify criteria to filter frmAddMeasure for reviewing purposes. The code under frmSearchMeasures creates RecordSource and Filter strings, feeds them to frmAddMeasure, and then viola, the frmAddMeasure is filtered by all the criteria on the main and subforms. Mostly. There are about 18 criteria between the two tables, and they come together in some very interesting ways. The filter is a little buggy but it's getting there.

Anyway. I happen to have a query that runs on the two tables that frmAddMeasure is based on, and outputs similar results to excel. After a user uses frmSearchMeasures to filter frmAddMeasure, I would like to user to be able to click a button to export all the results to excel. I think the easiest way to do this would be to run my query on the filtered results.

By now you are asking, why not just use the fields from frmSearchMeasures to narrow the query?

I have two problems. The first is that I heard (don't know if it's true) you can only use a max of six fields to do this, since access extrapolates all the possible combinations of filled in fields to create a SQL statement. With 18 fields, any of which can be null, that's 2^18, or over 250,000 possible combinations. I don't think access can handle that.

The other problem has to do with the nature of the data. The subject of the database is quality measures in healthcare, which are used by a number of different entities who either do, do not, or will, publicy report on these measures. This means that for each junction between measure and user, the user has three possible reporting statuses, but since each measure has many users, each measure can be "not reported and not pending," "reported by at least one and not pending by others," "reported by at least one and pending by others," "Not reported at all and Pending by at least one," etc. etc. The code under frmSearchMeasures figures out which measure falls into which category based on the reporting status of each of any number of measure users. Repeating this analysis with a query I think would be more complex and very time consuming.

So again the easiest thing, I think, would be to run a very simple query on the filtered table, but I don't know how to do this or even if it can be done. Any advice, at this point, would be extremely appreciated. I've been at this for quite a while now and I'm brand new to access and visual basic as of a few weeks ago.

Thanks!

Adam

Data doesn't exist in forms, it simply exists in tables. That means that there's no way of querying a form: you have to go back to the underlying table(s).

That having been said, I've never heard of a 6 field limit anywhere. Coming up with criteria based on 18 fields shouldn't be that big a deal.

If you've truly created a filter on the form, though, you should be able to retrieve the details of that filter (the Filter property of the form), and use that as the basis for your query. Unfortunately, though, you haven't really given enough information to allow me to provide more details.

Thanks doug. How do I go about making the filter property into a query? And what additional information do you need?

I appreciate it.

-Adam

Presumably you know the SQL that makes up the selection ignoring the filter. Let's say that:

SELECT Table1.Field1, Table1.Field2, Table2.Field2, Table3.Field2
FROM (Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field2)
INNER JOIN Table3 ON Table2.Field2 = Table3.Field1

You can add the Filter for the form to that using:

strSQL = "SELECT Table1.Field1, Table1.Field2, Table2.Field2, Table3.Field2 " & _
"FROM (Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field2) " & _
"INNER JOIN Table3 ON Table2.Field2 = Table3.Field1 " & _
"WHERE " & Me.Filter

Now, what you do with that SQL depends on what you're trying to do. You could set the RecordSource property of a form to that SQL, or you could simply use it as the SQL for a stored query.

Thanks. I'll give this a try and see how it goes!