I have a database with a main from where the user filters the primary recordset using Access's built-in filtering tools, like the right-click menu offering equals, does not equal, contains, does not contain, starts with, etc. I subsequently need to do things to all records the use has filtered.
I pull out the filtering text, using (form).Filter, and want to use that as a Where condition to restrict my code's actions to the records the user has filtered. As long as the filtering is on simple value fields, all works, but the user can also set those filters on fields that contain a code to a joined table. The filtering of course works fine, but the filtering text that Access generates is a horror show, from the point of VBA trying to parse it. Here is a simple example:
(([Lookup_cboOkres].[Okres]="Beroun")) AND ([Lookup_cboStat].[Stat]<>"Ceska republika" Or [Lookup_cboStat].[Stat] IS Null) AND (([Lookup_cboZem].[Zem] Like "*Norw*"))
Trying to use the text in the Where clause of a SQL Update statement throws an error – the 'Lookup_cbo' syntax apparently only works in the context of the form's filter clause, not in pure SQL.
I've also tried using the RecordsetClone of the main form's recordset and stepping through the records individually, but that is only practical when the number of records filtered is quite small.
Trying to dismantle the text of the filter clause and convert it to straight SQL is very difficult, since there are so many possibilities. The problem of dealing with this has cropped up many times over the years, and I've never developed a satisfactory solution. Is there any reasonable method for picking apart such filtering clauses and generating SQL from them? Or is there some other method altogether for doing this?
I pull out the filtering text, using (form).Filter, and want to use that as a Where condition to restrict my code's actions to the records the user has filtered. As long as the filtering is on simple value fields, all works, but the user can also set those filters on fields that contain a code to a joined table. The filtering of course works fine, but the filtering text that Access generates is a horror show, from the point of VBA trying to parse it. Here is a simple example:
(([Lookup_cboOkres].[Okres]="Beroun")) AND ([Lookup_cboStat].[Stat]<>"Ceska republika" Or [Lookup_cboStat].[Stat] IS Null) AND (([Lookup_cboZem].[Zem] Like "*Norw*"))
Trying to use the text in the Where clause of a SQL Update statement throws an error – the 'Lookup_cbo' syntax apparently only works in the context of the form's filter clause, not in pure SQL.
I've also tried using the RecordsetClone of the main form's recordset and stepping through the records individually, but that is only practical when the number of records filtered is quite small.
Trying to dismantle the text of the filter clause and convert it to straight SQL is very difficult, since there are so many possibilities. The problem of dealing with this has cropped up many times over the years, and I've never developed a satisfactory solution. Is there any reasonable method for picking apart such filtering clauses and generating SQL from them? Or is there some other method altogether for doing this?