Re: Wanting help regarding dynamic queries in MS-Access

From: Ben Yenko-Martinka <Ben.Martinka_at_livingimages.com>
Date: 1996/11/10
Message-ID: <328676FE.6DD8_at_livingimages.com>#1/1

E. Rijk wrote:
>
> I want some help regarding dynamic queries in MS-Access.
> I want to make a query where people can use one or more parameters as a
> criterium for the query. The parameters are entered in a form showing the
> several fields. If just one parameter is entered in a field, then the
> query should be limited to this field. If more than one field is filled
> in, then the query should consider these fields using the 'AND' operator.
> The problem is that the query is not allowed to look at the fields that
> are left blank. I solved it with the following construction (see later),
> which works well.
>
> This subroutine evaluates the contents of each field and assigns then a
> value to the several parameters. These are then summoned. This sum is then
> used to choose via a 'SELECT CASE' instruction the right query.
> Although this works well, the amount of code rapidly increases when the
> amount of fields is increased (2 to the power n, n being the amount of
> fields).
>
> Surely, there must be another way to do this, BUT HOW???
> Any help is highly appreciated!
> ---------------------------------------------------------------

My solution to this was as follows:

1. Create a function that gets passed a reference to a control and returns a boolean to determine whether the control is of a type that could be bound to a field, using a Select statement with TypeOf.
2. Loop through all the controls on the form using: For i = 0 to [Formname].Count - 1 If CanBeBound([Formname](i)) Then 'Do step 3 below End If Next i
3. For each bindable control, find out if it's bound to a field by checking to see if its ControlSource is null, and if not, looping through all the fields in the form's RecordSetClone in a manner similar to above, and compare each field to the Controlsource until a match is found. You might be able to do this faster by testing for an expression and otherwise assuming it's a valid field, but my method worked fine on a form with a high field density.
4. For each bound control whose value is not null, use .ControlSource + " = " + .Value to build your criteria, using dbType to make sure string values get enclosed in quotes and dates get enclosed in # signs. You can also use the BuildCriteria function if you desire. If you want to allow for wildcards and/or comparisons with NULL, then you have to get creative! (I did but won't detail it here - pretty complex stuff.)

If these functions are built properly, they can be generic and work with any such forms.

---

  __|__ Ben Yenko-Martinka    \0/ <Ben.Martinka_at_livingimages.com>

    (     President, Living Images, Inc.
    /     (http://www.livingimages.com)     
    \     "Blessed are the merciful, 
    |     for they shall receive mercy."                   
                            (Matthew 5:7)

Received on Sun Nov 10 1996 - 00:00:00 CET