A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using Form to Build SQL Statement



 
 
Thread Tools Display Modes
  #1  
Old May 5th, 2007, 05:31 PM
Jeff Monroe Jeff Monroe is offline
Member
 
First recorded activity by OfficeFrustration: May 2006
Location: San Diego, CA
Posts: 27
Default 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  
Old May 5th, 2007, 06:13 PM posted to microsoft.public.access.forms
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.