Parameter to select which column to search

[fyamoahaxracer]

Hi everyone, I have used parameters before to search a column but is there a way to use a parameter to select which column I want to search?

Example:
I have three columns: a, b, c
I was the query to ask for input and the user can select which column to search. I thought this would be simple but I must be missing something.

[Seth Schrock]

I don't know of a way to do this just using SQL, but it is very easy with a little VBA. I just use a combo box with the fields that I want to search and have a textbox for the value that I'm searching for. Then I use VBA to build the query string. From there, you can do all kinds of things including change a query's SQL code.

[fyamoahaxracer]

Thanks Seth! I was hoping to just use SQL but I do not think it is possible so I will probably have to use that method. Thanks for your help!

Edit: You don't happen to have a small sample of this do you?

[Seth Schrock]

I do somewhere, but it will take me a little while to find it. I will post it when I do (won't be until tomorrow).

[Seth Schrock]

Here is what I did. I have a textbox called txtValue, a combo box for my field list called cboField, and a button that performs the operation. In this case, I'm using a filter on my form and not editing the query's SQL.

This is in the button's OnClick event.

Dim strSearch As String
Dim strFilter As String
 
If Me.txtValue & "" <> "" And Not IsNull(Me.cboField) Then
    If Me.cboField = "CustSSN" Then
        strSearch = SSNSearchString(Me.txtValue)
    Else
        strSearch = Me.txtValue & "*"
    End If
 
    strFilter = Me.cboField & " Like """ & strSearch & """"
    Me.Filter = strFilter
    Me.FilterOn = True
Else
    MsgBox "You must provide both a search value and field to perform the search."
End If

[fyamoahaxracer]

SELECT [Query].Name AS Name, Cint(Count( [Query].[Name] )) AS [Count]
FROM [Query]
WHERE (((Len([Query].[COLUMN_NAME]))<>"1" And (Len([Query].[COLUMN_NAME]))<>"2"))
GROUP BY [Query].Name

Can you point me in the correct direction on how to use a combo box and VBA code for the above query? The combo box will have different values and set the value for COLUMN_NAME.

I created the combo box to be filled with the values from another table but am not sure where to start with this. I'm also not sure how to display a query on a form if the query is created with VBA code. I am looking into this now.

[Seth Schrock]

Please use [CODE/] tags when posting code.

In this case, you would need to edit the query's SQL because of the GROUP BY. So your code would then be

Dim strQuery As String
Dim db As DAO.Database
Dim qry As DAO.QueryDef
 
If Not IsNull(Me.cboField) Then
    Set db = CurrentDb
    Set qry = db.QueryDefs("Your Query Name Here")
 
    strQuery = "SELECT [Query].Name AS Name, Cint(Count( [Query].[Name] )) AS [Count] " & _
               "FROM [Query] " & _
               "WHERE (((Len(" & Me.cboField & "))<>1 " & _
               "And (Len(" & Me.cboField & "))<>2)) " & _
               "GROUP BY [Query].Name"
 
    qry.SQL = strQuery
 
    qry.Close
    Set qry = Nothing
    Set db = Nothing
 
    Me.Requery
Else
    MsgBox "You must provide a field to perform the search."
End If