If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Form to Build SQL Statement
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 |
#2
|
|||
|
|||
Using Form to Build SQL Statement
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 |
Thread Tools | |
Display Modes | |
|
|