Access Form - run query against table based on listbox selection
I have a table called HRData that includes a field called MRC
I have a form that has a listbox that sources the unique values for MRC an allows for one selection. If I want to use VBA to execute a query against the entire table and filter on MRC I am running into issues trying to reference the selection the value from the Listbox.
so base query would be
SELECT * FROM HRData
WHERE [MRC] = 'The selection in the list box'
How would this need to be adjusted if:
1. The listbox was muliti select and I need an IN statement instead.
SELECT * FROM HRData
WHERE [MRC] In ('Selecton 1' , 'Selection 2'... etc.
2. I wanted to filter against more than just MRC but also other fields like "Country" or "Job Function" that also have list box choices.
Thanks to all - i was able to piece the report together with the code provided. Have a much better understanding of how to iterate through the list box to get all values.
To use it as a filter for a form or report, it is the WHERE clause without the WHERE.
Generically
Me.Filter = "SomeNumberField" = Me.SomeSyntaxThatYieldsANu
Me.Filter = Me.Filter & " AND SomeStringField = " & chr(34) & Me.SomeSyntaxThatYieldsASt
Me.Filter = Me.Filter & " AND SomeDateField = #" & Me.SomeSyntaxThatYieldsADa