IIf statement, wildcard to return all records

A

adam.vogg

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
 
M

Michel Walsh

try, in SQL view:

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



Hoping it may help,
Vanderghast, Access MVP
 
F

fredg

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
Click to expand...

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

No need to specify .Value. Value is the default property.
 
A

adam.vogg

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])
 
F

fredg

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])
Click to expand...

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.
 
A

adam.vogg

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.
 
F

fredg

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.
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF Statement In Query Doesn't Work Right 3
WildCard in IIF Statement 2
wildcard number used in iif statement 4
Date comparison in IIF statement in a query 1
IIf Statement Error 3
IIf Statement in Query Criteria 1
IIf statement not working 4
IIf statement in query criteria 5

Top