Using an IIf statement to populate a combo box

[Guest]

Here is the SQL of my query:

SELECT ProjDetail.[Project Detail], ProjDetail.NeedPartNum
FROM ProjDetail
WHERE (((ProjDetail.NeedPartNum) Like IIf([Forms]![SavingsForm]![Part #]=""
Or [Forms]![SavingsForm]![Part #] Is Null,"No","*")));

I am trying to populate my Project Detail combo box with values from table
ProjDetail depending on what is entered to form field [Part #]. If the [Part
#] is null, then projects with a " NeedPartNum = No " should populate the
combo box, else all projects should be seen in the combo box.

I am using the SQL above for the RowSource of the Project Detail combo box
and tried to requery the [Part #] field after update. Currently only projects
with a "NeedPartNum = No" populate the project detail combo box regardless of
the Part # value.

Thanks for an ideas you might have..

 

[George Nicholson]

First thing I'd try would be to use IsNull() within your iif(), rather than
Is Null
(SQL likes Is Null but VBA likes IsNull(), and iif() is VBA):

IsNull([Forms]![SavingsForm]![Part #])

HTH,

 

[John Spencer]

Assumption:
NeedPartNum is a Yes/No (boolean) field.


SELECT ProjDetail.[Project Detail], ProjDetail.NeedPartNum
FROM ProjDetail
WHERE ProjDetail.NeedPartNum =
IIf([Forms]![SavingsForm]![Part #] Is Null,False,True) OR
Forms!SavingsForm![Part #] is Not Null

 

[Guest]

I tried your suggestion, but even when the [Part #] field is left blank in
the form, the query is not reading it as null.

First thing I'd try would be to use IsNull() within your iif(), rather than
Is Null
(SQL likes Is Null but VBA likes IsNull(), and iif() is VBA):

IsNull([Forms]![SavingsForm]![Part #])

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Here is the SQL of my query:

SELECT ProjDetail.[Project Detail], ProjDetail.NeedPartNum
FROM ProjDetail
WHERE (((ProjDetail.NeedPartNum) Like IIf([Forms]![SavingsForm]![Part
#]=""
Or [Forms]![SavingsForm]![Part #] Is Null,"No","*")));

I am trying to populate my Project Detail combo box with values from table
ProjDetail depending on what is entered to form field [Part #]. If the
[Part
#] is null, then projects with a " NeedPartNum = No " should populate the
combo box, else all projects should be seen in the combo box.

I am using the SQL above for the RowSource of the Project Detail combo box
and tried to requery the [Part #] field after update. Currently only
projects
with a "NeedPartNum = No" populate the project detail combo box regardless
of
the Part # value.

Thanks for an ideas you might have..