Can I do This-variable criteria for a field

[Guest]

I have a field in a table that can either get an integer or null...the
integers tie back to another table, null means that the record isn;t assigned
to any "region".

I want to have a single query that returns either ALL of the records or just
the "region" specified on a form. I thought I could add the Field to my
query grid and try the following for a criteria expression:

IIf([Forms]![frmTest]![cboRegion] Is Not
Null,[Forms]![frmTest]![cboRegion],Is Not Null Or Is Null)

But if the cboRegions is blank I get no results...makes sense looking at the
SQL, the field is being equated to "Is Not Null Or Is Null", which is
obviously not going to return anything.

I can resort to creating a nasty process in the form to create the SQL
statement, but would rather have a single query to do the whole job. My gut
says that this HAS to be possible, but after trying a couple times and
punting I'm wondering if I'm on a proverbial "wild goose chase"...

thanks...

 

[John Spencer (MVP)]

Try the following criteria.

Where (YourField = [Forms]![frmTest]![cboRegion] Is Null OR [Forms]![frmTest]![cboRegion])

 

[John Vinson]

I want to have a single query that returns either ALL of the records or just
the "region" specified on a form. I thought I could add the Field to my
query grid and try the following for a criteria expression:

IIf([Forms]![frmTest]![cboRegion] Is Not
Null,[Forms]![frmTest]![cboRegion],Is Not Null Or Is Null)

But if the cboRegions is blank I get no results...makes sense looking at the
SQL, the field is being equated to "Is Not Null Or Is Null", which is
obviously not going to return anything.

Try a criterion of

= [Forms]![frmTest]![cboRegion] OR [Forms]![frmTest]![cboRegion] IS
NULL

The second part of the OR will be true if the combo box is NULL - it's
not comparing anything to any table field, but if a WHERE clause is
true, it means "return the record"; so it will find all table records
if you don't select anything in the combo.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps