D
Damien McBain
OK I posted below a problem I had re filtering a subform based on the
contents of unbound controls (text & combo) on the main form with no
master/child links so I can get empty fields to return all values.
Chris D put me on what appears to be the right track by suggesting making
the recorsource of the subform a complete SQL statement.
Since I haven't the faintest clue about SQL and don't have time to learn
before this project's deadline, I tried adding criteria to the query in
design view based on this: http://www.mvps.org/access/queries/qry0001.htm
but it doesn't work properly.
There are 10 fields I want to filter by. I am experimenting with 2 of them.
In the query which is the recorsource of the subform, I have entered the
criteria as the name of the control on the main form. Under it I have
entered the same thing except with "Is Null" after it.
This creates extra columns in the query (after closing and re-opening) with
the name of the form field as the query field name and "Is Null" in the "OR"
criteria.
I use the Requery method to refresh the data in the subform.
When I have nothing in either of the experimental form fields, the subform
returns all records. When I enter something (which should return records) in
one of them, the subform returns nothing. Leaving that value there then
entering something in the other field, the subform returns the record I
expected.
Any idea what's going on & how to fix it?
contents of unbound controls (text & combo) on the main form with no
master/child links so I can get empty fields to return all values.
Chris D put me on what appears to be the right track by suggesting making
the recorsource of the subform a complete SQL statement.
Since I haven't the faintest clue about SQL and don't have time to learn
before this project's deadline, I tried adding criteria to the query in
design view based on this: http://www.mvps.org/access/queries/qry0001.htm
but it doesn't work properly.
There are 10 fields I want to filter by. I am experimenting with 2 of them.
In the query which is the recorsource of the subform, I have entered the
criteria as the name of the control on the main form. Under it I have
entered the same thing except with "Is Null" after it.
This creates extra columns in the query (after closing and re-opening) with
the name of the form field as the query field name and "Is Null" in the "OR"
criteria.
I use the Requery method to refresh the data in the subform.
When I have nothing in either of the experimental form fields, the subform
returns all records. When I enter something (which should return records) in
one of them, the subform returns nothing. Leaving that value there then
entering something in the other field, the subform returns the record I
expected.
Any idea what's going on & how to fix it?