IIf Is Null query criteria

S

shutzy

Registered User.

Local time

Today, 11:02

Joined

Sep 14, 2011

Messages

775

• Oct 29, 2012

• #1

i would like a list box to show all the records when the form opens but the combo box that is part of the criteria is blank when the form opens so nothing shows. ive tried an IIf but it dosnt seem to work. can anyone help with this

IIf(IsNull([Forms]![frmAppointmentTreatmentItems]![comboTreatmentFilter]),"",[Forms]![frmAppointmentTreatmentItems]![comboTreatmentFilter])

 

S

shutzy

Registered User.

Local time

Today, 11:02

Joined

Sep 14, 2011

Messages

775

• Oct 29, 2012

• #2

fantastico. thanks for your help. i thought that Is Null had to be followed by ().

can i ask how the query knows to show all the items when combo Is Null. it dosnt make sense to me. it works but i cant understand why.

 

Brianwarnock

Retired

Local time

Today, 11:02

Joined

Jun 2, 2003

Messages

12,667

• Oct 29, 2012

• #3

The query selects the record if the field value = the criteria value or the criteria value is null, since the criteria value will be null for every record all records will be selected.

Brian

 

S

shutzy

Registered User.

Local time

Today, 11:02

Joined

Sep 14, 2011

Messages

775

• Oct 31, 2012

• #4

thanks pat. its useful to know as i am learning this on the fly.

There is no reason to force the SQL engine to invoke the VBA library unnecessarily.

Click to expand...

would this slow the process down?