Null and Combo Box

[Sam]

I am working in Access 97 and building a form with buttons
and combo boxes for our users. The buttons run queries
using ODBC linked Oracle tables. The Oracle tables store
labor data for manufacturing recourses. Oracle can store
several versions of this data for each resource and each
version has a distinct name, except the primary one that
is used daily. The priamry name field is null in the
Oracle tables. My users need to be able to pull reports
for the various versions of this labor data based on each
resource labor name. I built a combo box that pulls the
distinct resource labor names. Of course the primary name
is blank in my combo box. I want the user to select the
name from the combo box, then click a button that runs a
query which uses the combo box in the query's Criteria
field to pull the labor data for the name they selected.
It all works well except the null name. I tried an IIF
([Combo box]![Name]=Null, Is Null, [Combo box]![Name])
statement in the query but this does not work.. I think
it fails because "Is Null" are reserved words. I have
used this same query for several years without the form.
When I open the query in Design View, I simply type "Is
Null" in the Criteria field for the resource labor name.
It always runs perfectly so I know my query is good. Any
ideas or suggestions on how to deal with Null in a combo
box would be greatly appreciated.

 

[Michael Keating]

Hi,

You won't be able to do this directly in the designer, you need to set the
designer to SQL view and then edit the sql string such that the 'Where'
condition is like :

WHERE IIf([Forms]![frmMyForm]![Combo box] Is Null,(TableName.ResourceName)
Is Null,(TableName.ResourceName) = [Forms]![frmMyForm]![Combo box])

so that the immediate if sets the whole where clause, not just the
conditional value.

HTH

MFK.