Use combo box null as criteria in query

[Guest]

Three combo boxes, cboDept, cboLine & cboEquip in one form called Form1.
None of the combo boxes has a Control Source. Row Source for cboDept is a
sumation query of the field DEPT in a table called tblEquip. Row Source for
cboLine is a sumation query of two fields in tblEquip, LINE & DEPT, with
'Forms!Form1!cboDept' as criteria for the DEPT field. Row Source for
cboEquip is a sumation query of three fields in tblEquip, DEPT, LINE & Equip.
'Forms!Form1!cboDept' is the criteria for DEPT field, 'Forms!Form1!cboLine'
is the criteria for LINE field.

The expected operation: User selects a department in cboDept. This
selection determines the population of cboLine, i.e. only those records in
tblEquip whose DEPT field match the selection from cboDept will be available
in cboLine droplist. User now makes a selection from cboLine. This
selection and the previous selection determine the population of cboEquip.

The works great until the selection from cboLine is a NULL, e.g. a
department has equipment that is not assigned to a line, therefore the LINE
field in tblEquip is NULL.

In a nut shell: When the criteria 'Forms!Form1!cboLine' for the LINE field
in the sumation query in the Row Source of cboEquip is NULL, the query
returns nothing.

NOTE: Manually putting 'IsNull' in place of 'Forms!Form1!cboLine' works.

Can you help me???

 

[Guest]

Del,

You can use this in the SQL statement to get the Null value into your list:

SELECT tablename.field FROM tablename UNION SELECT '(All)' FROM tablename
ORDER BY tablename.field ;

Then in your query write an if statement like:

IIf([Forms]![FormName]![Field]='(All)',[TableName]![Field],[Forms]![FormName]![Field])

I hope this helps