Temporarily Store data RRS feed

  • Question

  • Thank you for taking the time to read my question.

    I have a shared Access DB (2016). Users can build a filter and save the filter for use later. What they want now is the ability to edit the saved filter. The saved filter is essentially a long WHERE clause that I add to a SELECT statement.

    I'm not sure how to best parse out the pieces of the WHERE statement

    WHERE ((((tblImportedData.OriginatingLocation)LIKE'*LOT*') And ((tblImportedData.Process)LIKE'*Invoicing*')) Or (((tblImportedData.OriginatingLocation)LIKE'*LOT*') And ((tblImportedData.Process)LIKE'*Order Desk*')) Or (((tblImportedData.OriginatingLocation)LIKE'*LRO*') And ((tblImportedData.Process)LIKE'*Invoicing*')) Or (((tblImportedData.OriginatingLocation)LIKE'*LRO*') And ((tblImportedData.Process)LIKE'*Order Desk*')));

    which I build as the user adds fields, conditions and values. They can apply it and then choose to save it. Then I write it to a table and they can call it up again.

    Because I don't know how to reverse engineer the WHERE clause I thought I'd save the parameters and fields as it is built... however if the user decides not to save the filter, I've written data to a table for nothing, then I have to delete it. If several users are doing this at the same time, I will have to decide what to delete.

    So... my question.

    Is it possible to start an array that is stored outside of a procedure, like a global array? I tried making a global array on my form but Access doesn't like that. An array would automatically be temporary and if the user clicks my save button I can write the values of the array to the table.

    Not sure how to proceed. Any guidance would be greatly appreciated.

    Thursday, February 14, 2019 11:07 PM

All replies

  • Not sure how to proceed. Any guidance would be greatly appreciated.

    Hi mbrad,

    Where to begin … I think I have been working on the same kind of problem. I can try to describe how I solved this for myself.

    The users of my applications never go to the SQL-editor, but make use of a selection form where they can fill in their choices. This selection form is generic to all forms/tables in all of my (100+) applications! You can see an example:

    The users can for each available field/control (column "Veld") choose a value (column "Waarde") and an applicable condition (column "Conditie") that can be selected from column "Selecteer Conditie". The list of available selecties is refreshed according the type of the current "Veld" with appropriate values. With "joker" you can use the "*" and "?" as wild characters.

    After pressing the "Zoek" (Search) key the Where clause of the SQL-string is constructed, and the query is run. The values of column "Waarde" and column "Conditie" are stored in two hidden controls on the (calling) form, for re-use or slight modifications. This is done as the concatenation of the values in column "Waarde" and "Selectie".

    Certain more frequent selections can be stored in a table, and these are available in the column "Voorselectie", in this case "Jan".

    The underlined values in column "Veld" refer to foreign keys, where you can select on Id or on (part of) some field value.

    In the last column "Top" you can select on any number of last added records.

    There is a button "Id" to gather the data of a string of Id-values that are gathered in some other place.

    Going back to your question: the temporary selections I store ase a concatenated string of "Waarde" and "Selectie" on the calling form. These controls are refilled on re-opening the selection form. The where clause is generated dynamically, while more permanent Where clauses are stored in a permanent table (with nick name) in the formatted way rerady to use.

    I hope I was clear enough for to give you some possible way to solve the problem.


     Edit: "AND-woorden" and "OR-woorden" can be used for the condition that a field must contsin ALL specified words, viz AT LEAST ONE of the specified words.

    • Edited by Imb-hb Friday, February 15, 2019 10:04 AM edit
    Friday, February 15, 2019 9:53 AM
  • Hi Imb-hb,

    Thank you for your reply. Here is how I am currently doing things.

    Users select the field in the first combo box, the type of filter (>, >=.... contains, not contains... and so on. these options change based on the field type (text, number, date). They provide a value to filter on. Then if they want to continue to build the filter, the last combo box is And or Or. If they want to continue adding filter criteria, the click Add More Criteria, if not, they do not fill in the last combo box and they click filter. If the filter actually returns values, they have the option to save the filter with their own nickname. They can recall it under Apply Saved Filter. Users can save up to 10 filters.

    The box below the filter builder shows the plain criteria they have selected, but in a hidden text box I save the actual WHERE clause with all the brackets etc. If they save, then I save the hidden and the visible to a table, then just recall both when they use the Apply Saved Filter.

    The issue is they can't edit any of this. I am VERY limited for screen space in this db. The database continues to grow and I want to keep it as dynamic as possible so do not want to pre-define a form to build the WHERE.

    I'm not sure if any of this gives you some ideas....


    Friday, February 15, 2019 12:16 PM
  • The issue is they can't edit any of this. I am VERY limited for screen space in this db. The database continues to grow and I want to keep it as dynamic as possible so do not want to pre-define a form to build the WHERE.

    Hi mbrad,

    Though the basics for making dynamical selections are quite the same, the realization differs. I do not work in the form itself, but on each (overview) form, I have a button "Query", that opens the Selection form when clicked.

    In this way I am not limited to the dimensions or possibilities of the calling form. In the same way, when a user can define his "AND-woorden" or "OR_woorden", a new form is opened.

    The selection form itself is not specific for one form, but it is applicable to any table/query in any application. It is based on one and the same unbound form, which is tuned by the specific conditions of the calling form.

    The routines that manages this dynamical form are stored in a linked code library, so when I start a new application or add a new table to an existing application, the selection form is already there!

    In the selection form is it easy to edit the values or conditions from a previous call, because all is not yet converted to the final SQL-string.

    When building the SQL-string from the selections of the Selection form, I always use the construction: AND <veld> <condition> <value>. It is then very easy to add numerous other parts, but also to add other conditions that could be dependant on other conditions, e.g. a certain very specific time span.

    Finally, when composing the whole SQL-string, the very first "AND" is converted to "WHERE" to yield a valid string.

    All my applications (100+) are fully dynamical, and all are the same for more then 95%. A couple of definitions in meta data tables and a number of modules to tune the dynamical form in different ways, make the application specific for the end user.


    Friday, February 15, 2019 1:16 PM
  • My take on this would be to save the filter to a local table (if each built filter is specific to a user) or to a global table (if filters are to be shared between users).

    Then you just need a mechanism to save and restore filters to a table (or tables).

    You would use the same form to create or modify a filter.

    When modifying, you'd allow the option to 'save' or 'save as' to replace the original filter or create another one based on it.

    You cannot save the filter as a WHERE clause, you must save it in normalized form in a table or tables. Be able to read and write it and be able to form the WHERE clause from the table when needed.

    Unfortunately with this you took the quick and easy solution without considering the longer-term needs, but it should not be that difficult to remedy.

    Friday, February 15, 2019 5:12 PM