SQL Query

[ianbar]

Hi,

I have a search form that has a text field (txtsearchcriteria), and two drop down boxes (cbosearchwhat) and (cbostatus).

cbosearchwhat has the following options date, name, company.

cbostatus has all, closed and open

What I would like to do is have one SQL statement that processes the details on the form and outputs the details.

I'm not sure if this can be done, but if it can it would be a great use. another problem is 'all' needs to return a null value to the query so all records are selected.

 

[kraxmo]

If you create a Process command button named cmdProcess with an On Click event:

Sub cmdProcess_Click()
    Dim strSQL As String

    strSQL = "SELECT * FROM searchtable WHERE ( "
    strSQL = strSQL & Me!cbosearchwhat & " = " & Me!txtsearchcriteria & " )"

    If Me!cbostatus <> &quot;ALL&quot; Then
        strSQL = strSQL & &quot; AND ( Status = '&quot; & Me!cbostatus & &quot;' )&quot;
    End If

    Me.Recordsource = strSQL
End Sub

Jim Kraxberger
Developing Access solutions since 1995