Using Form to Build SQL Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Monroe - 05 May 2007 17:31 GMT

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

Signature

Jeff Monroe

Reply to this Message

Ken Snell (MVP) - 05 May 2007 18:13 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> 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
[quoted text clipped - 28 lines]
>
> Jeff

Reply to this Message