asked on

Query not returning data corresponding to filtering criteria

I have a form with 15 unbound text boxes into which I type sales order IDs. I then run a query which uses the form data as criteria. The criteria read like this:
Like "*" & [Forms]![SalesOrder]![ParentItemInput1] & "*" Or Like "*" & [Forms]![SalesOrder]![ParentItemInput2] & "*"

When I limit the criteria to just 2 inputs, the querie works just fine, but when I add others for Input3, Input4 and so forth, it returns completely unrelated sales orders. I can even clear the form and the query continues to return the same mysterious results.

Can someone please point out my error.

Thanks

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Personally, I would build the criteria for this query dynamically, at run time. To do so, create a private function in your forms code window, something like:

Private Function FilterForm

    Dim varCriteria as variant
    Dim intLoop as integer

    varCriteria = NULL
    for intLoop = 1 to 15
        if Trim(me.Controls("ParentItemInput" & intLoop) & "") <> "" then
            varCriteria = (varCriteria + " AND ") & "([FieldName] = " & me.controls("ParentItemInput" & intLoop) & ")"
        end if
    Next

    if Len(varCriteria & "") = 0 then
        me.subformcontrolname.form.filter = ""
        me.subformcontrolname.form.filteron = false
    else
        me.subformcontrolname.Form.Filter = varCriteria
        me.subformcontrolname.form.Filteron = true
    end if

End Function

Open in new window

Then you can either add a button in the header that calls this function, or you can select all of these 15 controls in design view, and modify the AfterUpdate event in the properties dialog to:

AfterUpdate: =FilterForm()

This way, you don't have to use the wildcards and add criteria for all 15 controls if only a couple are being used.

PatHartman

If you have more than 1 of something, you have many. Why hard code a specific number when you can easily use a table as your array?

Dale Fye

Pat,

I've just proposed a way to accomplish what the user wanted to implement. I don't disagree that many times, filtering a form your way is far more efficient, but if you have limited vertical real-estate, you might not want to put a multi-line subform in a forms header.

If I were implementing this in one of my applications (from scratch) I would probably do something along the lines of your suggestion (maybe with a popup filter form), but many times, I'm dealing with applications that were created by someone else, and am bound by constraints (labor) set by my clients, so when I reply to questions like these, I am frequently looking at how I might address this issue in an application which has been passed down to me, with the least amount of additional effort (hours).

Dale

Michael Moyer

ASKER

Thank you for your help. The table was a simple design that I was able to implement easily.

PatHartman

You're welcome.