handling nulls in mulitple parameter query

[haggisns]

Hi,

I have a a table with 10 columns.

I have put 10 parameters in it, one for each column

I have done the : like [parameter name] is null in the OR expression for
each field.

However this does not work.

If I enter non null for the first parameter and leave blank for the others, it will return the records for field1 with the parameter value but also return all the records that have nulls in the other fields.

I want to create a query that no matter what combination of non-null parameter values and null values fed to the 10 parameters I will always
get the records filtered down correctly.

I suspect this would need to be done using a sub procedure in VBA that would dynamically build the query by parsing together snippets of sql together until the correct SQL statement existed. Would this be right or can it indeed be done in just SQL?

Thanks

Haggisns

[weekend00]

Fields:        field1        field2          field3            field4 ...
Table:        tbl            tbl              tbl               tbl ...
Sort: 
Show: 
Criteria:   "a" or null      "b" or null     "C" or null     "D" or null
or:

[haggisns]

Thanks Weekend00 for the response.

However, I tried what you suggested and it does not work as I had hoped.

It returns the records that I give a parameter value for but it also return records that have null values.

I should point out that many records have null values in multiple fields.

I believe since my data is incomplete this SQL will not work.

[slave138]

I usually do these types of queries using a form with the Query By Form method.

For example, say the form has 3 fields (txtF1, txtF2, txtF3), then the query criteria would look something like:

Field1 = [Forms]![frmMyForm]![txtF1] Or [Forms]![frmMyForm]![txtF1] Is Null
Field2 = [Forms]![frmMyForm]![txtF2] Or [Forms]![frmMyForm]![txtF2] Is Null
Field3 = [Forms]![frmMyForm]![txtF3] Or [Forms]![frmMyForm]![txtF3] Is Null

This way, if I enter something in txtF1 but leave the other two blank, it will give me all records where Field1 = txtF1. If I enter values for txtF2 & txtF3, it will only give records where Field2 = txtF2 and Field3 = txtF3. etc...

I've never tried it using pop-up type parameters so I don't know if it would work the same (or at all).

[slave138]

If you want more info on the Query By Form method you can find it here (it's for Access 2002 but the method is pretty much the same):
http://support.microsoft.com/kb/304428/EN-US/

[haggisns]

Thanks everyone for your responses.

I experimented with a number of things, included the suggestions above and could'nt quite make a go of it with null data.

I found a VB snippet on the web that builds the query dynamically into a string value and creates the query using ado and querydefs, worked very nicely.

thanks again

Chris