Hello,

I am using an iif statement in a query criteria.  It draws data from a
dropdown box on an open form.  The dropdown box has only 3 possible
choices.  If the user does not choose a value, but leaves it blank, i
want the query to return all the records.

I read in a different post in here that i cant use 'Like' so right now
I have this in criteria:
IIf([forms]![frmSearch].[loadbox].[value]<>"",[forms]![frmSearch].
[loadbox].[value],"*")


It works fine when the user chooses an option, but returns nothing
when they choose nothing.  I also tried to make the false statement
""Option1" or "Option2" or "Option3"" and i got an error saying "you
canceled the previous operation".



help

try, in SQL view:

.... WHERE  iif([forms]![frmSearch]![loadbox] <>"", FieldNameHere = 
[forms]![frmSearch]![loadbox], true )



Hoping it may help,
Vanderghast, Access MVP


<adam.vogg@gmail.com> wrote in message 
news:036db8e0-df48-417f-b0ca-89a67f9dd596@y5g2000hsf.googlegroups.com...
> Hello,
>
> I am using an iif statement in a query criteria.  It draws data from a
> dropdown box on an open form.  The dropdown box has only 3 possible
> choices.  If the user does not choose a value, but leaves it blank, i
> want the query to return all the records.
>
> I read in a different post in here that i cant use 'Like' so right now
> I have this in criteria:
> IIf([forms]![frmSearch].[loadbox].[value]<>"",[forms]![frmSearch].
> [loadbox].[value],"*")
>
>
> It works fine when the user chooses an option, but returns nothing
> when they choose nothing.  I also tried to make the false statement
> ""Option1" or "Option2" or "Option3"" and i got an error saying "you
> canceled the previous operation".
>
>
>
> help 

On Wed, 12 Dec 2007 09:08:50 -0800 (PST), adam.vogg@gmail.com wrote:

> Hello,
> 
> I am using an iif statement in a query criteria.  It draws data from a
> dropdown box on an open form.  The dropdown box has only 3 possible
> choices.  If the user does not choose a value, but leaves it blank, i
> want the query to return all the records.
> 
> I read in a different post in here that i cant use 'Like' so right now
> I have this in criteria:
> IIf([forms]![frmSearch].[loadbox].[value]<>"",[forms]![frmSearch].
> [loadbox].[value],"*")
> 
> It works fine when the user chooses an option, but returns nothing
> when they choose nothing.  I also tried to make the false statement
> ""Option1" or "Option2" or "Option3"" and i got an error saying "you
> canceled the previous operation".
> 
> help

Set the criteria to:
forms!frmSearch!LoadBox or IsNull(forms!frmSearch!LoadBox)

No need to specify .Value. Value is the default property.
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Trying Michel's suggestion, i got an error on query execution saying
"query is typed incorrectly or too complex to evaluate".

With Fred's suggestion, i still get no results when the dropdown is
empty, when i want all the records.

so as of now the criteria is:
[forms]![frmSearch]![LoadBox] Or IsNull([forms]![frmSearch]![LoadBox])

On Wed, 12 Dec 2007 11:45:47 -0800 (PST), adam.vogg@gmail.com wrote:

> Trying Michel's suggestion, i got an error on query execution saying
> "query is typed incorrectly or too complex to evaluate".
> 
> With Fred's suggestion, i still get no results when the dropdown is
> empty, when i want all the records.
> 
> so as of now the criteria is:
> [forms]![frmSearch]![LoadBox] Or IsNull([forms]![frmSearch]![LoadBox])

please copy and paste the entire query SQL into a reply message.
Please indicate the Datatype of the criteria field, as well as the
bound column of the LoadBox and it's Rowsource SQL. 
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Entire SQL string of query:
SELECT tblCarrierList.CarrierName, tblCarrierList.Contact,
tblCarrierList.Phone, tblCarrierList.AfterHours, tblCarrierList.Fax,
tblCarrierList.Email, tblCarrierList.Units, tblCarrierList.Offices,
tblCarrierList.LoadType, tblCarrierList.Code, tblCarrierList.[L/E],
tblCarrierList.RegFlat, tblCarrierList.RegVan, tblCarrierList.HotShot,
tblCarrierList.PartCarrVan, tblCarrierList.Broker,
tblCarrierList.LocalCarrier, tblCarrierList.InterRail,
tblCarrierList.ContainerDray, tblCarrierList.Courier,
tblCarrierList.SE, tblCarrierList.NE, tblCarrierList.MW,
tblCarrierList.NW, tblCarrierList.GAonly, tblCarrierList.SW,
tblCarrierList.CN, tblCarrierList.MX
FROM tblCarrierList
WHERE (((tblCarrierList.LoadType)=[forms]![frmSearch]![LoadBox] Or
(tblCarrierList.LoadType)=IsNull([forms]![frmSearch]![LoadBox])));



Loadbox is a combo box.  No control source.  Row source =
"BOTH";"FULL";"PART.  Default Value = Null

LoadType (in the table) is a Text field.

On Wed, 12 Dec 2007 13:32:29 -0800 (PST), adam.vogg@gmail.com wrote:

> Entire SQL string of query:
> SELECT tblCarrierList.CarrierName, tblCarrierList.Contact,
> tblCarrierList.Phone, tblCarrierList.AfterHours, tblCarrierList.Fax,
> tblCarrierList.Email, tblCarrierList.Units, tblCarrierList.Offices,
> tblCarrierList.LoadType, tblCarrierList.Code, tblCarrierList.[L/E],
> tblCarrierList.RegFlat, tblCarrierList.RegVan, tblCarrierList.HotShot,
> tblCarrierList.PartCarrVan, tblCarrierList.Broker,
> tblCarrierList.LocalCarrier, tblCarrierList.InterRail,
> tblCarrierList.ContainerDray, tblCarrierList.Courier,
> tblCarrierList.SE, tblCarrierList.NE, tblCarrierList.MW,
> tblCarrierList.NW, tblCarrierList.GAonly, tblCarrierList.SW,
> tblCarrierList.CN, tblCarrierList.MX
> FROM tblCarrierList
> WHERE (((tblCarrierList.LoadType)=[forms]![frmSearch]![LoadBox] Or
> (tblCarrierList.LoadType)=IsNull([forms]![frmSearch]![LoadBox])));
> 
> Loadbox is a combo box.  No control source.  Row source =
> "BOTH";"FULL";"PART.  Default Value = Null
> 
> LoadType (in the table) is a Text field.

This part is not correct
Or (tblCarrierList.LoadType)=IsNull([forms]![frmSearch]![LoadBox])));

Change it to:
 WHERE tblCarrierList.LoadType)=[forms]![frmSearch]![LoadBox] 
 Or [forms]![frmSearch]![LoadBox] is Null;
 
 Notice that 'tblCarrierList.LadType =' is NOT repeated in the Or 
 clause. 
 I also changed IsNull(XXXX) to XXXX Is Null.
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Thanks Fred!!!

That worked great!