Multiply Query

  • Thread starter Thread starter dan.cawthorne
  • Start date Start date
D

dan.cawthorne

Hi All,

I was wondering how do i create a form type dialog with several
option, and depending on the options that are selected it filters out
the results,

Ive created a query, relating to me project table,

i have created a unbound form, and added all the required fields which
are combo's extra and the linked all the associated fields under the
criteria field on the query back to the dialog form i've created.

the dialog search form works fine, only if something is selected on
each field.

What I'm actualy asking is that if the a particular wanted to ignore
say the Quoted Combo and left it blank the query would display both
Quoted and Non Quoted projects, depending on what the other criteria
was used of course!

does that make sense?
 
Hi All,

I was wondering how do i create a form type dialog with several
option, and depending on the options that are selected it filters out
the results,

Ive created a query, relating to me project table,

i have created a unbound form, and added all the required fields which
are combo's extra and the linked all the associated fields under the
criteria field on the query back to the dialog form i've created.

the dialog search form works fine, only if something is selected on
each field.

What I'm actualy asking is that if the a particular wanted to ignore
say the Quoted Combo and left it blank the query would display both
Quoted and Non Quoted projects, depending on what the other criteria
was used of course!

does that make sense?
Click to expand...

What you need to do (and it sounds like you may already be doing it)
is to parse a SQL string based on the current settings of several on-
screen controls. Presumeably, the trigger is the change event for
each control. If some of these controls are textboxes which may be
blank, then you want to test for NULL, and parse the SQL command
accordingly.
I have found it useful to divide up the SQL string into substrings
each represented by a global variable with module scope and then
assemble them into the final string at the end.
 
I agree with OldPro. The way I usually do it is that I create a form level
subroutine that actually implements the filter (assuming I have the search
controls in the header or footer of the form I want to filter). You and call
it in the afterupdate event of each of the controls, or in the click event of
a "Filter" command button.

It might look like:

Private sub FilterForm

Dim varFilter as variant

varFilter = NULL

If len(me.txt_Somefield & "") > 0 then
varFilter = (varFilter + " AND ") _
& "[SomeField] = '" & me.txt_SomeField & "'"
END IF

If NOT ISNULL(me.cbo_SomeOtherField) then
varFilter = (varFilter + " AND ") _
& "[SomeOtherField] = " & me.cbo_SomeOtherField
END IF

me.filter = varFilter
me.filteron = true

End Sub

Note that in the first example (the textbox), I imbedded a single quote (')
inside of the quotes on either side of the reference to the control. I also
tested to see whether the length of the text in the control was zero. If the
control actually has a NULL value, then LEN( NULL ) will return an error, so
if you add an empty string to it, LEN(NULL & "") will return a zero. If this
value is greater than zero, then there is some text in the textbox.

In the second example, with the combo box, I made the assumption that the
bound column of that control is numeric, so I didn't have to wrap it in
single quotes.

You can add as many controls as you want to this process and it will only
use those that actually have a value in the filter.

HTH
Dale
 
Back
Top