I want to create a query based on input from many fields on a form, where not each of these fields must be filled in.
My questions:
1. How to make such a query?
2. What if I have, say 2 fields on the form, A and B where field A="10" and field B is not filled in. How to make a query that looks for records where field A="10" and field B can be anything (that's why field B wasn't filled in on the form).

Afterwards I want to use this query as recordsource for a new table.

Any help is welcome

Go here (Duane Hookom's website):

http://www.invisibleinc.com/download.cfm?filFilID=7

And download the Corp Tech Demos. It has an example of Query By Form.

Regards,

Beth M

Nice example but,

There you can select al the fields because every field is a record of a table and so you can loop through every record of that table.
In my application the different fields are text boxes and combos randomly arranged on a form. So how can I cycle though all the fields?

any suggestions?

Build your criteria by checking for not isnull value in your controls and build the criteria string as you go like in the following example:

Dim WhereStr As String
Dim SqlString As String
Dim db As Database

Set db = CurrentDb
SqlString = "Select From RecordSource Where "


If Not IsNull(f![MyDateField]) Then
   WhereStr = WhereStr & "DateField <= #" & f![MyDateField] & "#"
End If

If Not IsNull(f![NumField]) Then
   WhereStr = WhereStr & " AND NumField = " & f![NumField]
End If

'continue building your string of criteria
'put it all together
SqlString = SqlString & WhereStr
set rs = db.openrecordset(SqlString, dbopendynaset)

'close recordset when finished
rs.close
set rs = nothing
db.close
set db = nothing

thanks,

that's what I needed!