optional paramter query

[Souris]

I would like to give users optional criteria search option and return multi
records.

I would like to know are there any optional parameter for a query or I have
run different query for different number of parameters or I need build Select
SQL string at run time?

For example, the table has 4 composite key fields. User may just provide one
criteria to get multi records and user can select which one user wants to
select.

User does not need to know all the criteria.

Your help is great appreciated,

 

[KARL DEWEY]

A couple of ways. User can enter criteria in textbox or select from listbox
or combo box then command button to run query.
In the query have criteria that uses the textbox/listbox/combo one of these --
Like [Forms]![YourFormName]![TextBox] & "*"
OR
[Forms]![YourFormName]![TextBox] Or [Forms]![YourFormName]![TextBox] Is
Null

If all are place in the same criteria row then all select criteria must be
true to pull a record.

You can go further to have a Yes/No checkbox to AND all selection or to OR
the selection by including the checkbox in the query field row. Then use -1
or 0 (zero) as criteria in conjunction with the other criteria.
It would look like this ---
One Two Three Four AndOr
Bx1 Bx2 Bx3 Bx4 -1
Bx1 0
Bx2 0
Bx3 0
Bx4 0

 

[Souris]

Thanks milloins for the message,
The query works.
Because I use unbound form, I need get recordset.
I used "Set rs = qdfCurr.OpenRecordset()" to get recordset by pass parameter.
Are there any way to get recordset using the following query?

Thanks millions again,

A couple of ways. User can enter criteria in textbox or select from listbox
or combo box then command button to run query.
In the query have criteria that uses the textbox/listbox/combo one of these --
Like [Forms]![YourFormName]![TextBox] & "*"
OR
[Forms]![YourFormName]![TextBox] Or [Forms]![YourFormName]![TextBox] Is
Null

If all are place in the same criteria row then all select criteria must be
true to pull a record.

You can go further to have a Yes/No checkbox to AND all selection or to OR
the selection by including the checkbox in the query field row. Then use -1
or 0 (zero) as criteria in conjunction with the other criteria.
It would look like this ---
One Two Three Four AndOr
Bx1 Bx2 Bx3 Bx4 -1
Bx1 0
Bx2 0
Bx3 0
Bx4 0

--
KARL DEWEY
Build a little - Test a little

I would like to give users optional criteria search option and return multi
records.

I would like to know are there any optional parameter for a query or I have
run different query for different number of parameters or I need build Select
SQL string at run time?

For example, the table has 4 composite key fields. User may just provide one
criteria to get multi records and user can select which one user wants to
select.

User does not need to know all the criteria.

Your help is great appreciated,

 

[Souris]

I use following code

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Myquery")

I got "too few parameter expected" message.

Can you please help?

Thanks,