Using an IIf statement to populate a combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

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..
 
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,
 
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
 
I tried your suggestion, but even when the [Part #] field is left blank in
the form, the query is not reading it as null.

George Nicholson said:
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.


almb said:
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..
Click to expand...
Click to expand...
 
Back
Top