When you have two filtering options in a WHERE clause, you must provide the
field name in each subclause.
This will work:
SELECT * FROM TableName
WHERE Field1 Like "OneValue*" Or
Field1 Is Null;
This will not work:
SELECT * FROM TableName
WHERE Field1 Like "OneValue*" Or
Is Null;
You'll need to redo the way you build the SQL statements in your code so
that you can build correct WHERE clauses.
I have a sample database here that shows how to build various SQL WHERE
clauses based on choices made in controls on a form:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm
--
Ken Snell
MS ACCESS MVP
"Jeff Monroe" wrote in message
...
I have a form that gives a user three methods to dynamically filter an
existing report. Two of the selections are combo-boxes (Supplier and
Part No). The user selects a supplier(s), part number(s) then may
select from an options group (fraNCR_View) to determine if they want to
see one of three options: NCRs, NO NCRs or Both (either with or without
an entry in the NCR No field).
The trouble I'm having is viewing "Both". After selecting a Supplier
and Part No, NCR works (view the Supplier/Part No with NCRs), NO NCR
works (view Supplier/Part No without NCRs), but Both displays all
records from the table (All Suppliers and Part Nos with or without
NCRs).
Below is my Select Case statement:
Select Case Me.FraNCR_View.Value
Case 1
strNCR_View = "Is Not Null"
Case 2
strNCR_View = "Is Null"
Case 3
strNCR_View = "Like '*' or 'Is Null'"
End Select
And here is my string building a SQL statement that filters the
report:
strFilter = "[Supplier] " & strSupplier & " AND [PartNo] " &
strPartNo & " AND [NCR No] " & strNCR_View
Thanks.
Jeff
--
Jeff Monroe