Use form filter text in SQL

[pdanes]

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?

 

[theDBguy]

The primary tool to use for managing that would be the Replace() function. But to tame it, it would be preferable to apply it as you add the special control and hard code the name in it.

 

[pdanes]

The primary tool to use for managing that would be the Replace() function. But to tame it, it would be preferable to apply it as you add the special control and hard code the name in it.

I know how to use the Replace function, but how would you propose I use it here? I have to change the entire structure of the filter text, like I would have to change this:

([Lookup_cboZem].[Zem]="Beroun")

to this:

(Dlookup(0,"Zem","Zem = 'Beroun'") Is Not Null)

And that is the simplest example. When the conditions are stacked up, it becomes quite a mess.

 

[CJ_London]

there should be no need to disassemble the filter, just combine with the form recordsource

Sqlstr=“select * from (“ & recordsource & “) where “ & filter

apply the sqlstr to open a recordset or to a querydef

Note: on my phone which uses smart quotes

Edit: if your recordsource ends with a semi colon, you’ll need to remove it

Edit2: I note your filter is using the table name Lookup_cboStat and others. This should only happen when you have two tables with the same field name. This may be the case - or you are using select * in your recordsource and also bringing through the fields in additional columns in your recordsource

 

[pdanes]

But that is exactly what doesn't work. When the filter text contains this sort of stuff:

[Lookup_cboOkres].[Okres]

putting it into the Where clause of straight SQL throws an error, hence this post. If I could just use the filter text, I would be doing so.

 

[CJ_London]

see edit2 in my post

 

[theDBguy]

I know how to use the Replace function, but how would you propose I use it here? I have to change the entire structure of the filter text, like I would have to change this:

([Lookup_cboZem].[Zem]="Beroun")

to this:

(Dlookup(0,"Zem","Zem = 'Beroun'") Is Not Null)

And that is the simplest example. When the conditions are stacked up, it becomes quite a mess.

When I did it, I don't remember using the DLookup() function. I'll have to find an example to tell you how I did it.

Sent from phone...

 

[pdanes]

see edit2 in my post

The semi-colon? There are no semicolons in the form's filter clause.

 

[pdanes]

When I did it, I don't remember using the DLookup() function. I'll have to find an example to tell you how I did it.

Sent from phone...

I could make a Join, I suppose, but that would be even more complicated. But there has to be some tie to the auxiliary tables that feed the comboboxes. Since the text being filtered on is the text in that aux table, and the main table only contains codes that point into the aux tables, it is impossible to make the filters work without accessing the aux tables. Access forms do it with that Lookup_ syntax, but apparently that is specific to forms' filters.

 

[pdanes]

see edit2 in my post

I do have tables with the same field name. KodStat is the code (ID) of the Stat table, and the same field name is used in the main stable, since that is the field I link on, and both fields thereby contain the same thing - the Stat ID codes. The Stat table is the rowsource for the combobx. But Access forms allow filtering by the 'looked up' text in the aux table, and access that text via the Lookup_ syntax. The only way I know of to access that information from straight SQL is with the DLookup function, or something similar, but that requires parsing and translating the text that Access generates for those filtering specifications.

 

[CJ_London]

The semi-colon? There are no semicolons in the form's filter clause.

I was referring to the form record source

since that is the field I link on, and both fields thereby contain the same thing - the Stat ID codes.

So why are you (apparently) bringing both fields through in your recordsource?

Perhaps show your recordsource? Might put things into context

 

[June7]

I just tested this on a form that has comboboxes where the value is ID but displays text. The filter is built with the text not the combbox value. I think this is what OP is describing. Both fields are not in form recordsource, it is the combobox lookup that is issue. Those parameters have Lookup prefix, whereas those based on textboxes do not, example:

?Form_Games.Filter
((([Lookup_AwayTeam].[TeamName]="B"))) AND ([Games].[GameDate]=#6/30/2024#)

Issue is translating TeamName of "B" to TeamID of 2 so filter can be used elsewhere. Only solution I could imagine is a process that checks if certain fields are in filter string and if found perform DLookup to find associated ID value and Replace(). This could get very complicated because of dealing with parens and quote marks. I wouldn't do it, period.

I've never deployed a db that allowed users access to the ribbon or right click menu search. I built my own search utility.

 

[ebs17]

First of all, a filter is SQL, namely the content of a WHERE clause without the keyword WHERE. So nothing needs to be converted. For a better overview, you can remove the many unnecessary brackets.

but the filtering text that Access generates is a horror show

Therefore, the developer will not rely on superficial help from Access to put something together, but will be the boss in the ring and create filters using his own user interface.
If you make things too easy for yourself at the beginning, you will end up paying significantly more later.

Straight SQL ... should rather mean that you not only use the main table as the data source, but that this table is linked to the necessary lookup tables and in this way all the required fields are made available for operations such as filtering.

Straight SQL begins with looking in your relationship window and orienting yourself there as to which tables with which fields you need for your measures. You have to put these together in your query.

Once you know what it should look like, you can take what is useful from forms and existing designs.

 

[pdanes]

I was referring to the form record source


So why are you (apparently) bringing both fields through in your recordsource?

Perhaps show your recordsource? Might put things into context

The recordsource that I want to update, and which crashes when using the filter text, is built entirely by me in VBA. There is no semicolon.

I am not bringing in both fields. In fact, I am not brining in anything extra. The SQL that I am trying to execute is like this:

"Update ZapisNalezu Set OznA = True Where " & GBL_ZNForm.Filter

As long as that filter contains ONLY references to fields with data directly in the table ZapisNalezu, all is good - the statement executes with no problems. But when the filter includes a references to a combobox populated from a linked aux table, the text contains this 'Lookup_' syntax, which the Jet engine does not understand.

 

[pdanes]

I just tested this on a form that has comboboxes where the value is ID but displays text. The filter is built with the text not the combbox value. I think this is what OP is describing. Both fields are not in form recordsource, it is the combobox lookup that is issue. Those parameters have Lookup prefix, whereas those based on textboxes do not, example:

?Form_Games.Filter
((([Lookup_AwayTeam].[TeamName]="B"))) AND ([Games].[GameDate]=#6/30/2024#)

Issue is translating TeamName of "B" to TeamID of 2 so filter can be used elsewhere. Only solution I could imagine is a process that checks if certain fields are in filter string and if found perform DLookup to find associated ID value and Replace(). This could get very complicated because of dealing with parens and quote marks. I wouldn't do it, period.

I've never deployed a db that allowed users access to the ribbon or right click menu search. I built my own search utility.

If I had built this from scratch, maybe that would have been an option. But I came into this when the user already had an app, and knows something about how to use Access. That was years ago, and what the app is now is all my work, but she knows how to use things like right-click filters, and makes extensive use of them. I cannot just arbitrarily turn them off and tell her that she can't do that. This app is supposed to help her, not make her professional work unnecessarily more difficult.

And sadly, even the option of manually looking up things from the filter text is not ideal. The text can contain partial matches, such as ([Lookup_cboStat].[Stat] Like "Fred*"), which would require me to scan the Stat table and assemble all the IDs that match "Fred*". All this is doable, but a monumental coding PIA.

The main recordset is already filtered when my code comes into action. It seems like there should be some reasonable way to access that filtered recordset, but I have so far been unable to come up with anything.

 

[pdanes]

First of all, a filter is SQL, namely the content of a WHERE clause without the keyword WHERE. So nothing needs to be converted. For a better overview, you can remove the many unnecessary brackets.


Therefore, the developer will not rely on superficial help from Access to put something together, but will be the boss in the ring and create filters using his own user interface.
If you make things too easy for yourself at the beginning, you will end up paying significantly more later.

Straight SQL ... should rather mean that you not only use the main table as the data source, but that this table is linked to the necessary lookup tables and in this way all the required fields are made available for operations such as filtering.

Straight SQL begins with looking in your relationship window and orienting yourself there as to which tables with which fields you need for your measures. You have to put these together in your query.

Once you know what it should look like, you can take what is useful from forms and existing designs.

No - that's the whole problem - a filter is NOT simply SQL. It contains specifically this Lookup_ syntax, which is some added functionality that Access forms have on top of SQL. If what it generated was pure SQL, I would be using that and this thread would not exist.

All of what you write about 'looking in the relationship window' and such was done long ago, during the design phase of this project, and is exactly as it should be. But none of that has anything to do with the problem of user filtering by comboboxes.

 

[CJ_London]

I just tested this on a form that has comboboxes where the value is ID but displays text. The filter is built with the text not the combbox value

Ah, now I understand. Regret don’t have a solution except using replace.

An alternative might be to create a temporary table to store the PK's of filtered records (assuming this is the case) then perhaps your code would be

So an initial populate of the temp table

while not recordset.eof
db.execute "INSERT INTO tmpTable SET tmpPK =" & recordset.pk
recordset.movenext
wend

sqlstr="SELECT * FROM (" & recordsource & ") INNER JOIN tmpTable on recordsource.PK=tmpTable.PK"

Or have a hidden text box that you populate with a comma separated list of the pk’s and your sql becomes

sqlstr="SELECT * FROM (" & recordsource & ") Where pk in (“ & mid(textbox,2) & “)”

On my phone so be aware of the smart quotes

 

[ebs17]

UPDATE
   MainTable
SET
   FieldX = 123
WHERE
   ForeignID1 IN
      (
         SELECT
            ID
         FROM
            LookupTable1
         WHERE
            FieldY = "hello"
      )
   AND
   ForeignID2 =
      (
         SELECT
            ID
         FROM
            LookupTable2
         WHERE
            FieldZ = "0815"
      )

Here, too, you have to use the lookup tables so that you can use their fields.

Instead of the IN clause, you could also use JOIN, but Jet-SQL is quite stupid here and doesn't allow much. The design shown shows that it can be expanded and combined as desired. And again: The starting point should be knowing what a working query looks like.

and is exactly as it should be

This is assumed for the above proposal.

Lookup_cboOkres.Okres="Beroun"

Two steps take place here.
1) The lookup table is filtered.
2) The main table is filtered with the return value of the ComboBox.

 

[pdanes]

Ah, now I understand. Regret don’t have a solution except using replace.

An alternative might be to create a temporary table to store the PK's of filtered records (assuming this is the case) then perhaps your code would be

So an initial populate of the temp table

while not recordset.eof
db.execute "INSERT INTO tmpTable SET tmpPK =" & recordset.pk
recordset.movenext
wend

sqlstr="SELECT * FROM (" & recordsource & ") INNER JOIN tmpTable on recordsource.PK=tmpTable.PK"

Or have a hidden text box that you populate with a comma separated list of the pk’s and your sql becomes

sqlstr="SELECT * FROM (" & recordsource & ") Where pk in (“ & mid(textbox,2) & “)”

On my phone so be aware of the smart quotes

That did it. I made a temp table of one field - the ID numbers of the main table. I use a SQL delete statement to clear it, then step through a RecordsetClone of the main form's recordset, writing each ID into this temp table, and finally join to this temp table to perform the manipulations needed. I used a VBA DAO.Recordset rather than a SQL Insert statement, though - it is considerably faster.

I originally tried using the RecordsetClone to step through the filtered main table and update the fields directly, but that was impossibly slow - many minutes to grind through it all. This is well under one second to do the entire table, and far less when the form is filtered to the size of selections the user normally works with - a few hundred at the most, much more often around a dozen. The entire table is a bit over 11,000, and has grown during her normal work from just under 6,000 when I first got involved with this, 20 years ago. The rate of growth is not going to increase in any significant fashion, so I'd say I have a configuration that should go the distance.

Thank you for the idea. I started playing with regular expressions to parse the filter texts, and I may continue with that as a side project, but his takes care of what I needed to get working right now. Very much appreciate the steer.

 

[pdanes]

UPDATE
   MainTable
SET
   FieldX = 123
WHERE
   ForeignID1 IN
      (
         SELECT
            ID
         FROM
            LookupTable1
         WHERE
            FieldY = "hello"
      )
   AND
   ForeignID2 =
      (
         SELECT
            ID
         FROM
            LookupTable2
         WHERE
            FieldZ = "0815"
      )

Here, too, you have to use the lookup tables so that you can use their fields.

Instead of the IN clause, you could also use JOIN, but Jet-SQL is quite stupid here and doesn't allow much. The design shown shows that it can be expanded and combined as desired. And again: The starting point should be knowing what a working query looks like.


This is assumed for the above proposal.

Lookup_cboOkres.Okres="Beroun"

Two steps take place here.
1) The lookup table is filtered.
2) The main table is filtered with the return value of the ComboBox.

Yes, thank you - I do know how to do all that, but that was not the problem. The problem was not creating a filter from scratch - the problem was dealing with the filter texts that Access creates when the user filters by a combobox.

However, I have a working setup now. CJ_London wrote a suggestion in #17 that allows me to do it a different way and bypass dealing with the messy filter texts altogether.

But thank you for the ideas.