Filtering a query

[ListO]

I’m simplifying this question for brevity. The real database is more complex, but I can apply a viable answer to my database.

I have created a database with several of tables, such as NAME, TASK, CATEGORY.

I have a main display query which selects a collection of data taken from those tables. My main display form is based on that query and it works great. BUT---

I want users to be able to make selections in a “filtering” form which will be applied to the query. The filtering form has pull-down boxes to make selections from any of three tables. It also has an “Exclude” checkbox for each of the table selections.

After making a selection from each pull-down, the chosen record ID# from that table is assigned toa global variable which I have used for filtering (pName, pSection, pCategory, pNameExcl, etc)

This is what I’d like to accomplish:

• If no Name is selected from the NAMES pull-down, the query should return all records.
• If a Name is selected from the NAMES pull-down, the query should return only records with the selected name.
• If a name is selected from NAMES pull-down and a section is selected from the SECTION pull-down, the query should return only records that have the selected Name and Section.
• If a name is selected from the NAMES table, and an “Exclude” checkbox is checked, the query should return only records without the selected name.
• If no NAME is selected, I want the query to return all names.

I have more than the three tables represented in the filtering form, so this can get even messier.

I have been doing this by creating a big table with a Make Table query, then culling that list with delete or update queries, but that is somewhat clumsy.

I have been using the query builder, as I’m not at all proficient with SQL.

Is there a graceful way to apply those global variables to the main query?

 

[theDBguy]

Global variables and queries don't really see each other. You would normally use variables in VBA. Instead, you could try converting those variables into TempVars.

 

[tvanstiphout]

I’m simplifying this question for brevity. The real database is more complex, but I can apply a viable answer to my database.

I have created a database with several of tables, such as NAME, TASK, CATEGORY.

I have a main display query which selects a collection of data taken from those tables. My main display form is based on that query and it works great. BUT---

I want users to be able to make selections in a “filtering” form which will be applied to the query. The filtering form has pull-down boxes to make selections from any of three tables. It also has an “Exclude” checkbox for each of the table selections.

After making a selection from each pull-down, the chosen record ID# from that table is assigned toa global variable which I have used for filtering (pName, pSection, pCategory, pNameExcl, etc)

This is what I’d like to accomplish:

• If no Name is selected from the NAMES pull-down, the query should return all records.
• If a Name is selected from the NAMES pull-down, the query should return only records with the selected name.
• If a name is selected from NAMES pull-down and a section is selected from the SECTION pull-down, the query should return only records that have the selected Name and Section.
• If a name is selected from the NAMES table, and an “Exclude” checkbox is checked, the query should return only records without the selected name.
• If no NAME is selected, I want the query to return all names.

I have more than the three tables represented in the filtering form, so this can get even messier.

I have been doing this by creating a big table with a Make Table query, then culling that list with delete or update queries, but that is somewhat clumsy.

I have been using the query builder, as I’m not at all proficient with SQL.

Is there a graceful way to apply those global variables to the main query?

> several of tables, such as NAME
Is "NAME" really the name of one of your tables? That is a really bad idea. Name is a Reserved Word and using them is asking for trouble. Search online for "Access Reserved Words" and avoid them.

 

[tvanstiphout]

I’m simplifying this question for brevity. The real database is more complex, but I can apply a viable answer to my database.

I have created a database with several of tables, such as NAME, TASK, CATEGORY.

I have a main display query which selects a collection of data taken from those tables. My main display form is based on that query and it works great. BUT---

I want users to be able to make selections in a “filtering” form which will be applied to the query. The filtering form has pull-down boxes to make selections from any of three tables. It also has an “Exclude” checkbox for each of the table selections.

After making a selection from each pull-down, the chosen record ID# from that table is assigned toa global variable which I have used for filtering (pName, pSection, pCategory, pNameExcl, etc)

This is what I’d like to accomplish:

• If no Name is selected from the NAMES pull-down, the query should return all records.
• If a Name is selected from the NAMES pull-down, the query should return only records with the selected name.
• If a name is selected from NAMES pull-down and a section is selected from the SECTION pull-down, the query should return only records that have the selected Name and Section.
• If a name is selected from the NAMES table, and an “Exclude” checkbox is checked, the query should return only records without the selected name.
• If no NAME is selected, I want the query to return all names.

I have more than the three tables represented in the filtering form, so this can get even messier.

I have been doing this by creating a big table with a Make Table query, then culling that list with delete or update queries, but that is somewhat clumsy.

I have been using the query builder, as I’m not at all proficient with SQL.

Is there a graceful way to apply those global variables to the main query?

When there are a number of optional elements for a query, I like to compose the WHERE clause in code. Something like:
dim sql as string
sql = "select * from YourQuery where " & GetWhereClause()
Me.RecordSource = sql

Then we write GetWhereClause function, looking at the criteria and concatenating a string. Something like:
private function GetWhereClause() as string
dim s as string
s = "1 = 1 "
if not isnull(Me.Names) then s = s & "and FirstName = '" & Me.Names & "' "
'etc.
GetWhereClause = s
end function

 

[June7]

Apply filter to form or report instead of parameterizing query. Review http://allenbrowne.com/ser-62.html

Also, http://allenbrowne.com/AppIssueBadWord.html

 

[ListO]

> several of tables, such as NAME
Is "NAME" really the name of one of your tables? That is a really bad idea. Name is a Reserved Word and using them is asking for trouble. Search online for "Access Reserved Words" and avoid them.

No, it’s not the name of the table. Just trying to be clear with my question.