Complex query problem

[John]

Hi

I have a form with a number of multi-select lists and drop downs that user
can select to define a search criteria. The result then needs to be shown in
another list on the same form. Normally I would use a number of query
working in series (i.e. one query is source to the other and so on) with
initial queries using more exact criteria that works out fast and reduces
the number of records that following less exact queries need to search
through (speed is an issue).

The problem is that some query stages may not be required if user has not
selected anything from relevant dropdown/list. In this case how can I
achieve the flexibility of skipping certain query stages when needed? Should
I do everything in code to achieve the flexibility? In which case how can
one query be used as a source to another?

Thanks

Regards

[pietlinden]

you have to do it in code. you could define a string to hold the bits
of your query and then evaluate the pieces, adding criteria to your
query the user chooses values. if you're using a multi-select listbox
for query criteria, you don't have any choice, because .ItemsSelected
is a collection, and you'd have to iterate through it to get the
individual values anyway.

[Rich P]

Basically, you want to use default values. In Access, a default value
would be a wildcard like *. You could write a sql string something like
this:

Dim strSql As String, strParam1 As String, strParam2 As String

strParam1 = "'*'"
strParam2 = "*"

strSql = "Select * From tbl1 Where fld1 Like " & strParam1 _
& " And fld2 Like '" & strParam2 & "'"

for fld1 I will set the datatype as numeric, fld2 is text. Datatypes of
the Text type require single quote delimeters, but Numeric datatypes
don't require single quote delimeters. However, the wildcard * does
require single quote delimeters if used by text or numeric flds. In
fld2 I hardcode the single quotes into the sql string. But for fld1 I
will place the single quotes inside the strParam1 var.

Since VBA allows late binding, you can replace the value of strParam1
with a numeric value if needed. The default value is '*' for strParam1,
for strParam2 it is * (no single quotes since the single quotes are
hardcoded in the sql string already).

So if you have 5 listboxes, you would have strParam1...str Param5.
Depending on the datatypes (Dates would also be numeric) you assign the
default values. If a user picks a value from any of the listboxes, you
assign that value to the corresponding strParam.

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***