I have a table with data. Each record has dates and other data. I build a query to let user filter by time-period. In the query I use DataPart() in separate columns of the query just to segregate year ("YYYY") and month ("m") from the date of interest. I have a form with two combos: one with a list of years (e.g. 2020, 2021...) and the another one with a list of months (e.g. 1,2,3...) Everything works smoothly unless I select "Q1" in the combo (related to this string: 1 or 2 or 3 ,for example) that would end in an empty query. If I write 1 or 2 or 3 directly in the query criteria it works perfectly. Of course, if I write the same criteria with quotation marks ("1 or 2 or 3") it will fail the same way.
I tried hundreds of different ways to solve this, but I can't find a solution. Even with an IIF statement in the query criteria (e.g. IIF combo is Q1 then 1 or 2 or 3) won't work: whatever I write there different from a number will fail. I think that I am stuck with a dumb problem, sort of type mismatch, but unfortunately can't work it out. Every suggestion will be really appreciated. Thank you very much.
You can't do that unless you use VBA to rewrite the SQL like this if the combobox returns "1, 2, 3":
SQL = "Select * From Table Where DatePart('m', [DateField]) In (" & [YourMonthComboBox] & ")"
Or, here the RowSource, split the value returned from the combobox if it can return, say, "M1", "M2", or "Q1" into its parts - for Q1, "Q" and "1":
Select * From Table Where DatePart(Mid([YourMonthComboBox], 1, 1), [DateField]) = Mid([YourMonthComboBox], 2)