Need help on query to use value or all values

[angelo0000]

I have a query which looks at a textbox and does the search based on what is put in the text box. EX: in my database ihave values a, b, c, d. If i type in a in the textbox the query will return all records with a. I would like to be able to type all in the text box and it return all records and not care if it is a, b, c, or d. Here is what I have thus far:
SELECT Stories.Story, Stories.Iteration, Stories.[Story Owner], Actions.Action, Actions.State, Actions.ExpectedResult, Actions.StoryID, Stories.NextJetID, Stories.Project
FROM Stories INNER JOIN Actions ON Stories.StoryID = Actions.StoryID
WHERE (((Stories.Iteration)=[Forms]![Report Form]![AllTestsForIteration].[ListIndex]+"6&quot AND ((Stories.Project)=[Forms]![Report Form]![Text79].[Value]));


My problem is that I dont know how to conditionalize this sql query to say if value = 'All'

 

[jimmidopolus]

Have you thought of giving a score to what is entered in the box and then refering it to a select case to modify the query's underlying SQL?

If you are just using the 'WHERE' Clause on the same fields you could simply write the following to determine your SQL:

eg.

strSQL = "SELECT Stories.Story, Stories.Iteration, Stories.[Story Owner], Actions.Action, Actions.State, Actions.ExpectedResult, Actions.StoryID, Stories.NextJetID, Stories.Project
FROM Stories INNER JOIN Actions ON Stories.StoryID = Actions.StoryID"

strWHERE = "WHERE (((Stories.Iteration)=[Forms]![Report Form]![AllTestsForIteration].[ListIndex]+"6&quot AND ((Stories.Project)=[Forms]![Report Form]![Text79].[Value]));"


if not([txt entered]="All&quot then strSQL = strSQL & " " & strWHERE

------

All you need to then do is write a procedure to modify the qry SQL statement eg.

Sub ModifyQrySQL(QryName As String, strSQL1 As String)
'This prints the value to the immediate window to test it passes through correctly
Debug.Print strSQL1

'Variable Declarations
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command

On Error GoTo Proc_Error
'Create new objects

Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
'Set connection

cat.ActiveConnection = CurrentProject.Connection

'Open the Query's SQL command (Views as simple query else Procedures is used)
Set cmd = cat.Views(QryName).Command

'Set the SQL to that passed by the function
cmd.CommandText = strSQL1

'Save the Query
Set cat.Views(QryName).Command = cmd

'Clean Up
Set cat = Nothing

Proc_Exit:
Exit Sub

Proc_Error:
MsgBox Err.Description
Resume Proc_Exit

End Sub

Hope Helps!

jimmid