combo-box parameter for query criteria to filter data in a subform

[Chris Buchanan]

I have a subform that is going to be filtered using the the results of a
field on a different form. I have been doing this for years and it works
well. However, I've changed what I'm doing a bit and now I can't get the
query to work. I've looked around the forums, but haven't found anything yet
that seems to answer my question.

I have a look-up table (LUT) with a field [Project]. However, this LUT may
not have entries for every single project for which I store data. What the
LUT does contain is a set of default values where [Project]= "zgene".

The form with the combo box ([select_project]) that provides a parameter to
multiple queries through-out my database shows every single project contained
in the database, including a null option.

The new sub-form uses the parameter to filter the LUT, where the LUT is used
to provide a value for a field in the sub-form. Now, I've been able to get
two of the three results that I require with a simple IIf statement, but not
the third. Here are the three results I think I need:

1. a null selection filters the "zgene" project values.

2. value for [select_project] is not present in the LUT and the values for
"zgene" are filtered.

3. value for [Select_project] is present in the LUT and these values are
filtered.

I can get results 1 and 3, but not 1, 2, and 3. If anyone has a hint of how
to do this I would appreciate it.

On the other hand if this makes no sense of all I'll understand too. Thanks
for any insights you may be able to offer.

 

[June7]

Might help to see the code.

 

[Chris Buchanan]

Here is the SQL code generated by Access for the typical query that I use to
filter the Look-up table.

SELECT LTlithology.rock_code, LTlithology.Description
FROM LTlithology
WHERE (((LTlithology.Project)=IIf([Forms]![Switchboard]![select_project] Is
Null,"zgene",[Forms]![Switchboard]![select_project])));

In my original post I think I implied that this query was filtering data for
the whole subform, but in fact it is just for the row source of a combo-box
in the subform. This particular query fullfills results 1 and 3 in my
original post. But, I'm stimied on how to get it to fulfill all three
results.

Please keep in mind that I am a database amateur, I use them to to store my
data, but I have now programming experience.

--
Chris Buchanan
Geologist

Might help to see the code.

I have a subform that is going to be filtered using the the results of a
field on a different form. I have been doing this for years and it works
well. However, I've changed what I'm doing a bit and now I can't get the
query to work. I've looked around the forums, but haven't found anything yet
that seems to answer my question.

I have a look-up table (LUT) with a field [Project]. However, this LUT may
not have entries for every single project for which I store data. What the
LUT does contain is a set of default values where [Project]= "zgene".

The form with the combo box ([select_project]) that provides a parameter to
multiple queries through-out my database shows every single project contained
in the database, including a null option.

The new sub-form uses the parameter to filter the LUT, where the LUT is used
to provide a value for a field in the sub-form. Now, I've been able to get
two of the three results that I require with a simple IIf statement, but not
the third. Here are the three results I think I need:

1. a null selection filters the "zgene" project values.

2. value for [select_project] is not present in the LUT and the values for
"zgene" are filtered.

3. value for [Select_project] is present in the LUT and these values are
filtered.

I can get results 1 and 3, but not 1, 2, and 3. If anyone has a hint of how
to do this I would appreciate it.

On the other hand if this makes no sense of all I'll understand too. Thanks
for any insights you may be able to offer.

 

[June7]

I am still might not be quite understanding the structure of your tables,
correct where needed:
1. Two tables, a master list of Projects, and a lookup of values.
2. Some fields will have 'zgene' to represent Null OR some other value.
3. Here is where I was really unsure: Project is field in both tables and is
the key to link them, if so, Project cannot be 'zgene'; a record may not be
present in the LUT for a project.

Are you familiar with Access domain functions like DLookup? Could use
DLookup on the LUT to see if Project is present and use this result in query,
could even build the DLookup right into the query.

Here is the SQL code generated by Access for the typical query that I use to
filter the Look-up table.

SELECT LTlithology.rock_code, LTlithology.Description
FROM LTlithology
WHERE (((LTlithology.Project)=IIf([Forms]![Switchboard]![select_project] Is
Null,"zgene",[Forms]![Switchboard]![select_project])));

In my original post I think I implied that this query was filtering data for
the whole subform, but in fact it is just for the row source of a combo-box
in the subform. This particular query fullfills results 1 and 3 in my
original post. But, I'm stimied on how to get it to fulfill all three
results.

Please keep in mind that I am a database amateur, I use them to to store my
data, but I have now programming experience.

--
Chris Buchanan
Geologist

Might help to see the code.

I have a subform that is going to be filtered using the the results of a
field on a different form. I have been doing this for years and it works
well. However, I've changed what I'm doing a bit and now I can't get the
query to work. I've looked around the forums, but haven't found anything yet
that seems to answer my question.

I have a look-up table (LUT) with a field [Project]. However, this LUT may
not have entries for every single project for which I store data. What the
LUT does contain is a set of default values where [Project]= "zgene".

The form with the combo box ([select_project]) that provides a parameter to
multiple queries through-out my database shows every single project contained
in the database, including a null option.

The new sub-form uses the parameter to filter the LUT, where the LUT is used
to provide a value for a field in the sub-form. Now, I've been able to get
two of the three results that I require with a simple IIf statement, but not
the third. Here are the three results I think I need:

1. a null selection filters the "zgene" project values.

2. value for [select_project] is not present in the LUT and the values for
"zgene" are filtered.

3. value for [Select_project] is present in the LUT and these values are
filtered.

I can get results 1 and 3, but not 1, 2, and 3. If anyone has a hint of how
to do this I would appreciate it.

On the other hand if this makes no sense of all I'll understand too. Thanks
for any insights you may be able to offer.