Setting Is Null criterion programmatically

[Bakema]

Hi there,

I am populating a listbox in form by a select query
whereby I set the criteria what to select through
comboboxes on a form, which are referenced in the
criteria section of the query. All works fine apart from
setting the criteria Is Null and Is Not Null.

My approach has been to reference an empty (invisible)
textbox on the form in the criteria section, but that
returns an empty recordset. I have also tried "", same
problem.

Of course a solution is to use a separate query with the
Is Null criterion typed in the crieria section, but I
would really like to know if the Is Null and Is Not Null
crieria for a query can be set programmatically from a
control's or combobox afterupdate event. In my case it
would substantially reduce on the number of queries in
the database.

Bakema

 

[Joe Fallon]

I like this syntax:

Like iif(isnull(Forms![FormName]![cboName]), '*',
Forms![FormName]![cboName])

 

[Steve Schapel]

.... otherwise expressed as:
Like Nz([Forms]![FormName]![cboName],"*")

However, if I understand the question correctly, this will not do it.
If you mean you want the query to return all records where the field
in question is blank if the criteria selection combo is left blank,
you will need to put something like this in the query criteria...
[Forms]![MyForm]![MyCombo] Or (Is Null And [Forms]![MyForm]![MyCombo]
Is Null)

- Steve Schapel, Microsoft Access MVP

 

[Joe Fallon]

Steve,
I agree.
I thought long and hard about qualifying my response and decided that wasn't
what the poster meant.
I thought the poster meant: if I leave the cbo blank how can I get it to
still return some records.

Qualifier:
In almost all cases I code default values so I do not have any Null fields
in my tables. So my syntax always works (for me.)
--
Joe Fallon
Access MVP

... otherwise expressed as:
Like Nz([Forms]![FormName]![cboName],"*")

However, if I understand the question correctly, this will not do it.
If you mean you want the query to return all records where the field
in question is blank if the criteria selection combo is left blank,
you will need to put something like this in the query criteria...
[Forms]![MyForm]![MyCombo] Or (Is Null And [Forms]![MyForm]![MyCombo]
Is Null)

- Steve Schapel, Microsoft Access MVP

I like this syntax:

Like iif(isnull(Forms![FormName]![cboName]), '*',
Forms![FormName]![cboName])