Using wildcards in iif in expression builder

[filip]

I'm stuck in creating a selection criteria for a query.
Based on a value in a Form, I need to select all records which are "Yes", if
this text in the form is "SMD" or, if this value is not "SMD", I want all
records (both "Yes" and "No").
The first part of the iif statement is no problem and works fine, but how
can I select all records (what do I need in the falsepart of the iif)?
It looks like I need to use a wildcard here to select all records but I
can't make it work.

IIf([Forms]![Frm_DATA Extract]![Area]="SMD";"Yes"; )

 

[Allen Browne]

Switch the Query to SQL View (View menu.)
Locate the WHERE clause.
Change it to look like this:
WHERE IIf([Forms]![Frm_DATA Extract]![Area]="SMD", True,
[YourYesNoField])

This works because the WHERE clause is something that evaluates to True or
False (or Null) for each record. The expression above evaluates to TRUE for
every record when the Area text box contains SMD. If the text box doesn't
contain that, the WHERE clause evaluates to True only if the text box is
true (checked.)

 

[filip]

Switch the Query to SQL View (View menu.)
Locate the WHERE clause.
Change it to look like this:
WHERE IIf([Forms]![Frm_DATA Extract]![Area]="SMD", True,
[YourYesNoField])

This works because the WHERE clause is something that evaluates to True or
False (or Null) for each record. The expression above evaluates to TRUE for
every record when the Area text box contains SMD. If the text box doesn't
contain that, the WHERE clause evaluates to True only if the text box is
true (checked.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm stuck in creating a selection criteria for a query.
Based on a value in a Form, I need to select all records which are "Yes",
if
this text in the form is "SMD" or, if this value is not "SMD", I want all
records (both "Yes" and "No").
The first part of the iif statement is no problem and works fine, but how
can I select all records (what do I need in the falsepart of the iif)?
It looks like I need to use a wildcard here to select all records but I
can't make it work.

IIf([Forms]![Frm_DATA Extract]![Area]="SMD";"Yes"; )

Allen,
Thanks for your comments, but I still didn't make it work. This selection
criteria is only one of the many criterias in this selection query.
Underneath you can find the full SQL view. Everything is working fine except
this above mentioned problem.

SELECT Tbl_DCAFS_Data.Date, Tbl_DCAFS_Data.NoTec, Tbl_DCAFS_Data.Model,
Tbl_DCAFS_Data.Comp, Tbl_DCAFS_Data.PartNo, Tbl_DCAFS_Data.Defect,
Tbl_lookup_ProfitCtr.[Profit Ctr], Tbl_lookup_ProfitCtr.Area,
IIf(Tbl_Assemblies!SMD=Yes And Tbl_Problem_Classificatie!SMD=Yes,"Yes","No")
AS SMD, IIf([Station]="Insercion-plant160","Yes","No") AS Visual
FROM Tbl_Problem_Classificatie INNER JOIN ((Tbl_lookup_ProfitCtr INNER JOIN
Tbl_Assemblies ON Tbl_lookup_ProfitCtr.[Profit Ctr] = Tbl_Assemblies.[Profit
Ctr]) INNER JOIN Tbl_DCAFS_Data ON Tbl_Assemblies.DCAFS_Model_Name =
Tbl_DCAFS_Data.Model) ON Tbl_Problem_Classificatie.Defect =
Tbl_DCAFS_Data.Defect
WHERE (((Tbl_DCAFS_Data.Date)>=[Forms]![Frm_DATA Extract]![Start_Date] And
(Tbl_DCAFS_Data.Date)<=[Forms]![Frm_DATA Extract]![Stop_Date]) AND
((Tbl_DCAFS_Data.Model)=IIf([Forms]![Frm_DATA Extract]![Model] Is Not
Null,[Forms]![Frm_DATA Extract]![Model],[Tbl_DCAFS_Data]![Model])) AND
((Tbl_DCAFS_Data.PartNo)=IIf([Forms]![Frm_DATA Extract]![Part] Is Not
Null,[Forms]![Frm_DATA Extract]![Part],[Tbl_DCAFS_Data]![PartNo])) AND
((Tbl_DCAFS_Data.Defect)=IIf([Forms]![Frm_DATA Extract]![Defect] Is Not
Null,[Forms]![Frm_DATA Extract]![Defect],[Tbl_DCAFS_Data]![Defect])) AND
((Tbl_lookup_ProfitCtr.Area)=IIf(([Forms]![Frm_DATA Extract]![Area]<>"ALL")
And ([Forms]![Frm_DATA Extract]![Area]<>"TSUB") And ([Forms]![Frm_DATA
Extract]![Area]<>"SMD"),[Forms]![Frm_DATA Extract]![Area],[Area])) AND
((IIf([Tbl_Assemblies]![SMD]=Yes And
[Tbl_Problem_Classificatie]![SMD]=Yes,"Yes","No"))=IIf([Forms]![Frm_DATA
Extract]![Area]="SMD","Yes")) AND
((Tbl_DCAFS_Data.AreaAffected)=IIf([Forms]![Frm_DATA
Extract]![Area]="TSUB","Manual/Final-Plant160-TSUB",[Tbl_DCAFS_Data]![AreaAffected])))
ORDER BY Tbl_DCAFS_Data.Date;

 

[Allen Browne]

As you found, that WHERE clause is not going to work.

If the Date field is null, it won't match anything. Therefore the record
will be rejected regardless of whether the text boxes on the form have
anything in them or not. (Or perhaps [Date] is a required field, in a simple
query where the field cannot be null?")

Similarly, this phrase:
AND ((Tbl_DCAFS_Data.Model)=
IIf([Forms]![Frm_DATA Extract]![Model] Is Not Null,
[Forms]![Frm_DATA Extract]![Model],
[Tbl_DCAFS_Data]![Model]))
compares the Model field to either:
a) the Model box on the form if that's not null, or else
b) itself.
Again, if the field itself is null, then comparing it to itself will not
match, since Null doesn't match Null. If that's news, see the first example
in:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

To correct that use:
AND IIf([Forms]![Frm_DATA Extract]![Model] Is Null, TRUE,
[Tbl_DCAFS_Data]![Model] = [Forms]![Frm_DATA Extract]![Model])

The same problems will occur with the other phrases in the WHERE clause.

While it is possible to craft a convoluted WHERE clause such that the IIf()
do evaluate to TRUE when the text boxes are null, it gets unweildy, not to
mention inefficient to execute. An alternative approach is to build the
WHERE clause from only the boxes on the form where the user actually entered
something. You can then use this WHERE clause as the Filter for a form, the
WhereConditon for OpenReport, or even to dynamically assign the SQL of the
QueryDef if you need to.

If you are interested in that approach, download the sample database from
this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It may take half an hour or so to digest this, but it will be time well
spent, as it's so flexible.

Oh, and BTW, a field named Date is not a good idea: Access will misinterpret
it as the system date in some contexts, and spit the dummy in other contexts
(queries.) Here's a list to refer to and avoid when designing tables:
http://allenbrowne.com/AppIssueBadWord.html

Hope the dynamically generated filter opens new (and simpler) possibilties
for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Switch the Query to SQL View (View menu.)
Locate the WHERE clause.
Change it to look like this:
WHERE IIf([Forms]![Frm_DATA Extract]![Area]="SMD", True,
[YourYesNoField])

This works because the WHERE clause is something that evaluates to True
or
False (or Null) for each record. The expression above evaluates to TRUE
for
every record when the Area text box contains SMD. If the text box doesn't
contain that, the WHERE clause evaluates to True only if the text box is
true (checked.)

I'm stuck in creating a selection criteria for a query.
Based on a value in a Form, I need to select all records which are
"Yes",
if
this text in the form is "SMD" or, if this value is not "SMD", I want
all
records (both "Yes" and "No").
The first part of the iif statement is no problem and works fine, but
how
can I select all records (what do I need in the falsepart of the iif)?
It looks like I need to use a wildcard here to select all records but I
can't make it work.

IIf([Forms]![Frm_DATA Extract]![Area]="SMD";"Yes"; )

Allen,
Thanks for your comments, but I still didn't make it work. This selection
criteria is only one of the many criterias in this selection query.
Underneath you can find the full SQL view. Everything is working fine
except
this above mentioned problem.

SELECT Tbl_DCAFS_Data.Date, Tbl_DCAFS_Data.NoTec, Tbl_DCAFS_Data.Model,
Tbl_DCAFS_Data.Comp, Tbl_DCAFS_Data.PartNo, Tbl_DCAFS_Data.Defect,
Tbl_lookup_ProfitCtr.[Profit Ctr], Tbl_lookup_ProfitCtr.Area,
IIf(Tbl_Assemblies!SMD=Yes And
Tbl_Problem_Classificatie!SMD=Yes,"Yes","No")
AS SMD, IIf([Station]="Insercion-plant160","Yes","No") AS Visual
FROM Tbl_Problem_Classificatie INNER JOIN ((Tbl_lookup_ProfitCtr INNER
JOIN
Tbl_Assemblies ON Tbl_lookup_ProfitCtr.[Profit Ctr] =
Tbl_Assemblies.[Profit
Ctr]) INNER JOIN Tbl_DCAFS_Data ON Tbl_Assemblies.DCAFS_Model_Name =
Tbl_DCAFS_Data.Model) ON Tbl_Problem_Classificatie.Defect =
Tbl_DCAFS_Data.Defect
WHERE (((Tbl_DCAFS_Data.Date)>=[Forms]![Frm_DATA Extract]![Start_Date] And
(Tbl_DCAFS_Data.Date)<=[Forms]![Frm_DATA Extract]![Stop_Date]) AND
((Tbl_DCAFS_Data.Model)=IIf([Forms]![Frm_DATA Extract]![Model] Is Not
Null,[Forms]![Frm_DATA Extract]![Model],[Tbl_DCAFS_Data]![Model])) AND
((Tbl_DCAFS_Data.PartNo)=IIf([Forms]![Frm_DATA Extract]![Part] Is Not
Null,[Forms]![Frm_DATA Extract]![Part],[Tbl_DCAFS_Data]![PartNo])) AND
((Tbl_DCAFS_Data.Defect)=IIf([Forms]![Frm_DATA Extract]![Defect] Is Not
Null,[Forms]![Frm_DATA Extract]![Defect],[Tbl_DCAFS_Data]![Defect])) AND
((Tbl_lookup_ProfitCtr.Area)=IIf(([Forms]![Frm_DATA
Extract]![Area]<>"ALL")
And ([Forms]![Frm_DATA Extract]![Area]<>"TSUB") And ([Forms]![Frm_DATA
Extract]![Area]<>"SMD"),[Forms]![Frm_DATA Extract]![Area],[Area])) AND
((IIf([Tbl_Assemblies]![SMD]=Yes And
[Tbl_Problem_Classificatie]![SMD]=Yes,"Yes","No"))=IIf([Forms]![Frm_DATA
Extract]![Area]="SMD","Yes")) AND
((Tbl_DCAFS_Data.AreaAffected)=IIf([Forms]![Frm_DATA
Extract]![Area]="TSUB","Manual/Final-Plant160-TSUB",[Tbl_DCAFS_Data]![AreaAffected])))
ORDER BY Tbl_DCAFS_Data.Date;