Create a multiple parameter search form for a query

[angie]

I need to figure out how to create a user interface to search a query,
but here's the bad part...I need to account for criteria on at least 7
of the fields. Here's what I'm thinking I need to do:

Create an unbound form with unbound fields for all 7 of the fields in
the query. Then in the query create parameters that refer to those
fields.

But it's not that simple because the user needs the flexibility of
filling in as many or as few of the fields as they want. For example,
they may have "account number", a "customer agent number" and a "date
opened" and an "account type" to search by, or maybe they want to see
all accounts of a certain type only.

Is there any easier way to do this? All I can picture is writing lines
and lines of criteria for all the combinations of "ANDs" and/or "ORs"
that this would require. If there's a different road to go down, in VB
or something, I'm all for it, anything that could be cleaner than
this...

[Bob Quintal]

"angie" <ub*******@hotmail.com> wrote in
news:11**********************@m73g2000cwd.googlegr oups.com:

I need to figure out how to create a user interface to search
a query, but here's the bad part...I need to account for
criteria on at least 7 of the fields. Here's what I'm
thinking I need to do:

Create an unbound form with unbound fields for all 7 of the
fields in the query. Then in the query create parameters that
refer to those fields.

But it's not that simple because the user needs the
flexibility of filling in as many or as few of the fields as
they want. For example, they may have "account number", a
"customer agent number" and a "date opened" and an "account
type" to search by, or maybe they want to see all accounts of
a certain type only.

Is there any easier way to do this? All I can picture is
writing lines and lines of criteria for all the combinations
of "ANDs" and/or "ORs" that this would require. If there's a
different road to go down, in VB or something, I'm all for it,
anything that could be cleaner than this...

Do you need to filter the query, or can you filter the
form/report which has the query as a recordsource?

If you can, it's easier.

Either way, build an unbound form with the controls, And/Or
selectors and a "Do It" button.
In the OnClick Event for the button, build a SQL where clause
based on the contents of the fields and the setting of the AndOr
selectors.

Dim strWhere as string
If len(me.txtLastname) & "" > 0 then
strWhere = strWhere & iif(me.Andor1 = "And", " and ", " OR
")
strWhere = strWhere & "[table].[lastname] = """ & txtLastname
& """"
end if
If len(me.txtFirstname) & "" > 0 then
strWhere = strWhere & iif(me.Andor1 = "And", " and ", " OR
")
strWhere = strWhere & "[table].[Firsttname] = """ &
txtFirstname & """"
end if

.....
' when done strip off the first and /or
strWhere = mid(strWhere,5)

'If you are opening a form or report, just pass the whereclause
as a parameter in the relevant
DoCmd.Openform "formname",,,strWhere

If it's a query you have to create a recordset and muck around
with the full SQL string. How this is done depends on how you
want to use the query, ask if you need to.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Appelq]

On your Prompts form, you should have a [Submit] button that will have
an On-click event to hold some VBA code.

Dim qd As DAO.QueryDef
Dim Whereclause As String
Dim ssql As String
Dim Assignment As Integer

ssql = "SELECT * From table"

IF me!AccountNumber Not NULL Then
Whereclause = " WHERE AccountNumber = " & Me!AccountNumber
End If

IF CustomerAgent Not NULL then
IF Whereclause is NULL Then
whereclause = " WHERE CustomerAgent = " & me!CustomerAgent
Else
Whereclause = " AND CustomerAgent = " & me!CustomerAgent
End IF
END IF

' Make IF statements for each field on the Prompts form.
' At the end you will have a Whereclause that has entries for those
fields that were filled in; or possibly Whereclause will be NULL.

IF Whereclause Not NULL then
ssql = ssql & Whereclause
End IF

'Then execute the ssql or save it as a query and then do whatever other
VBA code you want.