VBA Passing function argument for query criteria as variable

Question

I've been using VBA for a year or more now, however I'm completely self taught and far from advanced. I've used StackOverflow to solve my problems countless times, however this is my first question, so please forgive any issues.

I have a form with a textbox that I would like to use as criteria for a query. I can currently get the query to run if my textbox contains only 1 value that I want to search for, however if I want to search for more than one value, I run into issues.

I followed this process to get as far as I have Using Variables in VBA to Filter Access 2007 Queries

My plan was to either pass a criteria as a string using a function something like criteria: GetCrit() where GetCrit is a public function as string in a separate module, returning the string In("Value1",Value2") Then my thought was to simply set the argument as a variable defined by the function GetCrit(). Ie: the query criteria would be In(GetCrit()).

I'm not able to get either of these options to work, as I believe currently the query is looking for values that satisfy the string "In(GetCrit())" due to my function being defined as a string.

I was planning on changing the textbox to a combo or listbox containing all possible values, then using a macro to turn the selections into a a string which can be read similar to In("Value1","Value2"), but I'm open to suggestions for better ways to fulfill this.

shown below is my applicable code

within Form:

Private Sub btnGen_Click()
SetQueryCriteria
End Sub

Public Sub SetQueryCriteria()
Dim MatrixCriteria As String
Dim holder As String
holder = [Forms]![frm_Name]![TXTBox].Value
SetCrit holder
End Sub

in module1:

Private strCrit As String

Public Sub SetCrit(Value As String)
   strCrit = Value
End Sub

Public Function getcrit() As String
    getcrit = strCrit
End Function

And the Criteria for my Query is currently

In(GetCrit())

Thanks!

EDIT: As I continue researching, I'm finding that perhaps a Multi Select Listbox may be what I want to use here

Selecting multiple search parameters from the same field using multi-select listbox is valid approach. Review allenbrowne.com/ser-50.html. — June7

Erik A Erik A · Accepted Answer · 2017-04-13T17:24:56

You can't filter by a single function in this way, since the function returns a single string and a single string can't contain multiple items to filter on.

You can work around it in the following way:

Public Sub AlterQuery()
    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("Query1")
    qdf.SQL = "SELECT * From Table Where Something In (" & GetCrit() & ")"
End Sub

This hardcodes the IN criterium in your query. If you open your query from your form, you can run this code before the query opens.

VBA Passing function argument for query criteria as variable

1 Answers